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

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.

CodePudding user response：

try using MAX with group by

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

CodePudding user response：

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;

CodePudding user response：

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