Select last 7 days records by date and avoid duplicates by specific column

user_5012780 0
Viewed 71

I'm pretty sure the problem I'm facing is quite small, but I can't figure out what's wrong.

I have the following table in my sql:

+-----+------------------+---------------------+
| ID  | EMAIL            | VISIT               |
+-----+------------------+---------------------+
| 1   | john@email.com   | 2021-04-01,13:20:23 |
| 2   | peter@email.com  | 2021-04-03,12:03:44 |
| 3   | daniel@email.com | 2021-04-04,13:21:12 |
| 4   | john@email.com   | 2021-04-06,09:34:31 |
| 5   | peter@email.com  | 2021-04-07,11:20:22 |
+-----+------------------+---------------------+

I want to display the last 7 days records but ordered by newest date and showing only the newest record by each email, like this

+-----+------------------+---------------------+
| ID  | EMAIL            | VISIT               |
+-----+------------------+---------------------+
| 3   | daniel@email.com | 2021-04-04,13:21:12 |
| 4   | john@email.com   | 2021-04-06,09:34:31 |
| 5   | peter@email.com  | 2021-04-07,11:20:22 |
+-----+------------------+---------------------+

I've tried this query to achieve this:

Attempt 1: SELECT * FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW() GROUP BY EMAIL ORDER BY VISIT DESC

Attempt 2: SELECT DISTINCT (EMAIL) FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW() ORDER BY VISIT DESC

The results are displayed correctly, but in a strange order. If I give up GROUP BY clause, it displays correctly but also includes EMAIL column duplicates.

date sql php
3 Answers

try using MAX with group by

SELECT EMAIL,MAX(VISIT)  as last_visit FROM table WHERE VISIT BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()  GROUP BY EMAIL ORDER BY last_visit DESC
user_112365630

I suggest this code to you: ‌

SELECT
    EMAIL
FROM `table_name`
WHERE
    VISIT BETWEEN(NOW() - INTERVAL 7 DAY) AND NOW()
GROUP BY
    EMAIL
ORDER BY
    MAX(VISIT)
DESC;
user_104267180

You can add a subquery to number the rows within each group, then select from this subquery only the first rows in each group:

SELECT EMAIL, VISIT FROM (
    SELECT EMAIL, VISIT, ROW_NUMBER() OVER (PARTITION BY EMAIL ORDER BY VISIT DESC) AS 'RowNumber' 
    FROM table 
    WHERE VISIT>DATE_SUB(NOW(), INTERVAL 7 DAY)
) T1
WHERE RowNumber=1
user_4095330
Related