CODEWITHSUNDEEP
mysqldatabaserdbms
margherita pizza
margherita pizza

Reputation: 7145

MySQL group by with max value

Hi I have this table.

id  lat     lng     userId
1   12      23      1
2   45      34      2
3   42      34      3
4   33      34      1
5   36      79      2
6   53      98      2
7   23      90      3
8   23      67      1

Here we have three users. (user ids 1,2,3). I want to get lateset record (id column max value) of each user. My excepted output is this

userId  lat     lng
1       23      67
2       53      98
3       23      90

This query will give me group by option

SELECT 
    *
FROM
    covid.locations
GROUP BY userId;

But how do I combine this with MAX(id) function.

Upvotes: 0

Views: 888

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

One way is to use the following:

SELECT
    cl.*
FROM covid.locations cl
INNER JOIN (
        SELECT
            userid
          , MAX( id ) mid
        FROM covid.locations
        GROUP BY
            userId
    ) g ON cl.userid = g.userid
    AND cl.id = cl.mid

Another is to use row_number() over()

SELECT
    userId
  , lat
  , lng
FROM (
    SELECT
        *
      , ROW_NUMBER() OVER (PARTITION BY userid ORDER BY id DESC) rn
    FROM covid.locations
    GROUP BY
        userId
) d
WHERE rn = 1

Both will identify the "most recent" row in the source table based in the id column of that table. Note that the second query requires MySQL version 8+ as this is when row_number() became supported in that database. The first query should run in dbms supporting SQL.

Upvotes: 4

user2560539
user2560539

Reputation:

This will do 

SELECT
*
FROM
covid.locations
where id in (select max(t.id) from covid.locations t group by t.userId)
order by id desc;

An example of the above query can be found in this SQLFiddle

Upvotes: 2

Related Questions