-1

For the purpose of this question, I have three fields in a MySQL table: id, userId, and loginDate (DATETIME). I would like to return the latest record for each userId per day. How can I create a query to do the following in section B below?

A. Records

id userId loginDate
1 5 2021-01-01 00:05:50
2 7 2021-01-01 06:06:50
3 5 2021-01-01 06:34:50
4 3 2021-01-02 06:56:76
5 3 2021-01-02 15:46:52

B. What I would expect to be returned from the query

id userId loginDate
2 7 2021-01-01 06:06:50
3 5 2021-01-01 06:34:50
5 3 2021-01-02 15:46:52
astentx
  • 6,393
  • 2
  • 16
  • 25
oREMughY
  • 1
  • 1

2 Answers2

1

For MySQL 8 you can use row_number analytic function to order your timestamps inside the day in descending order, and then select the first item per group. No tricky aggregation, limiting and joins, it just assigns the number according to the order and grouping, the rest of the row is unchanged.

with a as (
  select 1 as id, 5 as userid, timestamp '2021-01-01 00:05:50' as logindate union all
  select 2, 7, timestamp '2021-01-01 06:06:50' union all
  select 3, 5, timestamp '2021-01-01 06:34:50' union all
  select 4, 3, timestamp '2021-01-02 06:56:56' union all
  select 5, 3, timestamp '2021-01-02 15:46:52'
)
, rn as (
  select a.*,
    row_number() over(
      partition by
        userid,
        date(logindate)
      
      order by
        logindate desc
    ) as __rn
  from a
)
select *
from rn
where __rn = 1
order by userid desc

id | userid | logindate           | __rn
-: | -----: | :------------------ | ---:
 2 |      7 | 2021-01-01 06:06:50 |    1
 3 |      5 | 2021-01-01 06:34:50 |    1
 5 |      3 | 2021-01-02 15:46:52 |    1

db<>fiddle here

astentx
  • 6,393
  • 2
  • 16
  • 25
-2

You should use GROUP BY with the help of some aggregate functions.

SELECT max(id) AS id, userId, max(loginDate) AS loginDate FROM table GROUP BY userId ORDER BY id ASC;

return:

id  userId  loginDate
2   7   2021-01-01 06:06:50
3   5   2021-01-01 06:34:50
5   3   2021-01-02 15:46:52

Explanation: GROUP BY get's confused by your query because multiple rows may match. Using the max() aggregate function allows us to return only the newest rows data.

GrumpyCrouton
  • 8,486
  • 7
  • 32
  • 71