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

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   | [email protected]   | 2021-04-01,13:20:23 |
| 2   | [email protected]  | 2021-04-03,12:03:44 |
| 3   | [email protected] | 2021-04-04,13:21:12 |
| 4   | [email protected]   | 2021-04-06,09:34:31 |
| 5   | [email protected]  | 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   | [email protected] | 2021-04-04,13:21:12 |
| 4   | [email protected]   | 2021-04-06,09:34:31 |
| 5   | [email protected]  | 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.

phpsqldate


Solution 1:[1]

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

Solution 2:[2]

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;

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 AlirezaAhmadi
Solution 3

Related Questions