Retrieving the last record in each group - MySQL

we will look at how you can use MySQL at getting the last record in a Group By of records. For example if you have this result set of posts.

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

I want to be able to get the last post in each category which are Title 3, Title 5 and Title 6. To get the posts by the category you will use the MySQL Group By keyboard.

select * from posts group by category_id

But the results we get back from this query is.

id category_id post_title

1 1 Title 1

4 2 Title 4

6 3 Title 6

The group by will always return the first record in the group on the result set.

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

This will return the posts with the highest IDs in each group.

id category_id post_title

3 1 Title 3

5 2 Title 5

6 3 Title 6

Reference Click Here

I've not yet tested with large DB but I think this could be faster than joining tables:

SELECT *, Max(Id) FROM messages GROUP BY Name

SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )

**

Hi, this query might help :

**

SELECT 
  *
FROM 
  message 

WHERE 
  `Id` IN (
    SELECT 
      MAX(`Id`) 
    FROM 
      message 
    GROUP BY 
      `Name`
  ) 
ORDER BY 
   `Id` DESC

i find best solution in https://dzone.com/articles/get-last-record-in-each-mysql-group

select * from `data` where `id` in (select max(`id`) from `data` group by `name_id`)

If you need the most recent or oldest record of a text column in a grouped query, and you would rather not use a subquery, you can do this...

Ex. You have a list of movies and need to get the count in the series and the latest movie

SELECT COUNT(id), series, SUBSTRING(MAX(CONCAT(id, name)), LENGTH(id) + 1), 
FROM Movies
GROUP BY series

This returns...

MAX will return the row with the highest value, so by concatenating the id to the name, you now will get the newest record, then just strip off the id for your final result.

More efficient than using a subquery.

So for the given example:

SELECT MAX(Id), Name, SUBSTRING(MAX(CONCAT(Id, Other_Columns)), LENGTH(Id) + 1), 
FROM messages
GROUP BY Name

Happy coding, and "May The Force Be With You" :)

If performance is really your concern you can introduce a new column on the table called IsLastInGroup of type BIT.

Set it to true on the columns which are last and maintain it with every row insert/update/delete. Writes will be slower, but you'll benefit on reads. It depends on your use case and I recommend it only if you're read-focused.

So your query will look like:

SELECT * FROM Messages WHERE IsLastInGroup = 1

MariaDB 10.3 and newer using GROUP_CONCAT.

The idea is to use ORDER BY + LIMIT:

SELECT GROUP_CONCAT(id ORDER BY id DESC LIMIT 1) AS id,
       name,
       GROUP_CONCAT(Other_columns ORDER BY id DESC LIMIT 1) AS Other_columns
FROM t
GROUP BY name;

db<>fiddle demo

Another approach :

Find the propertie with the max m2_price withing each program (n properties in 1 program) :

select * from properties p
join (
    select max(m2_price) as max_price 
    from properties 
    group by program_id
) p2 on (p.program_id = p2.program_id)
having p.m2_price = max_price

Hope below Oracle query can help:

WITH Temp_table AS
(
    Select id, name, othercolumns, ROW_NUMBER() over (PARTITION BY name ORDER BY ID 
    desc)as rank from messages
)
Select id, name,othercolumns from Temp_table where rank=1

What about:

select *, max(id) from messages group by name 

I have tested it on sqlite and it returns all columns and max id value for all names.

As of MySQL 8.0.14, this can also be achieved using Lateral Derived Tables:

SELECT t.*
FROM messages t
JOIN LATERAL (
  SELECT name, MAX(id) AS id 
  FROM messages t1
  WHERE t.name = t1.name
  GROUP BY name
) trn ON t.name = trn.name AND t.id = trn.id

db<>fiddle

Here is a more efficient version in 1 line, works as long as the table has a time stamp column.

SELECT Id, Name, SUBSTRING_INDEX(MAX(CONCAT(TimeStamp, ',', Other_Columns)), ',', -1)
FROM Messages
ORDER BY id DESC GROUP BY Name 

This will return the latest record for the group on "Other_Columns"

Yet another option without subqueries.

This solution uses MySQL LAST_VALUE window function, exploiting Window Function Frame available MySQL tool from .

SELECT DISTINCT 
    LAST_VALUE(Id)            
        OVER(PARTITION BY Name 
             ORDER     BY Id 
             ROWS BETWEEN 0 PRECEDING 
                      AND UNBOUNDED FOLLOWING),
    Name,
    LAST_VALUE(Other_Columns)            
        OVER(PARTITION BY Name 
             ORDER     BY Id 
             ROWS BETWEEN 0 PRECEDING 
                      AND UNBOUNDED FOLLOWING)
FROM   
    tab

Try it here.

You can group by counting and also get the last item of group like:

SELECT 
    user,
    COUNT(user) AS count,
    MAX(id) as last
FROM request 
GROUP BY user

I had a similar issue

Subquery and join to the rescue

SELECT p."Date"
        ,p."Symbol"
        ,p."ratio_roll_qtr_ret"
    FROM PUBLIC."prices_vw" AS p
    JOIN (
        SELECT "Symbol"
            ,max("Date")
        FROM PUBLIC."prices_vw"
        GROUP BY "Symbol"
        ) AS sq ON p."Date" = sq."max"
        AND p."Symbol" = sq."Symbol"
    WHERE p."ratio_roll_qtr_ret" IS NOT NULL
    ORDER BY "ratio_roll_qtr_ret" DESC;