SQL how to find ids of rows with largest created_date inside a grouped by

Question

I have a table with three columns:

id | created_at | original_id
a1 | 2019-12-10 | a
a2 | 2019-12-12 | a
a3 | 2019-12-11 | a
b1 | 2019-12-10 | b
b2 | 2019-12-09 | b

I want to find the ids of all rows that have the largest created_at within all rows with the same original_id

(Background: the table stores the "history" of an element, so each time it is changed or when it is deleted, a new history-entry is created. I want to know the ids of the newest history-entry for each element/for each original_id)

So what I would expect as a result for the above example data would be:

id | MAX(created_at) | original_id
a2 | 2019-12-12      | a
b1 | 2019-12-10      | b

I have tried several ways of using subqueries and group-by but I can't really get the max value inside a grouped by

SELECT a.id, a.original_id, a.created_at FROM notes.test AS a
WHERE a.created_at = (SELECT MAX(b.created_at)
                FROM notes.test AS b
                WHERE b.original_id = a.original_id)

After I figure out how to even write this query with sql I also need to use it with a Java-Spring-Repository so any ideas on how to implement it there are also welcome

Your query works fine you just need to add the table alias to the first instance of `test` i.e. `SELECT a.id, a.original_id, a.created_at FROM test a` http://www.sqlfiddle.com/#!9/af76c/2 — Nick, Dec 20 '19 at 08:23
I agree with @nick the query works fine. see http://www.sqlfiddle.com/#!9/4bdd3d/1 — P.Salmon, Dec 20 '19 at 08:26
yeah, I just found that too^^ Thank you so much for the help! — Juliette, Dec 20 '19 at 08:30

score 1 · Answer 1 · answered Dec 20 '19 at 08:44

1

In MySQL 8 you can use window functions:

select original_id
     , id as last_id
     , created_at
  from ( select original_id
              , created_at
              , id
              , row_number()
                  over (partition by original_id
                        order by created_at desc)
                  as row_num
           from test
       ) x
 where row_num = 1

Output

original_id | last_id | created_at
a           | a2      | 2019-12-12
b           | b1      | 2019-12-10

See DEMO on db-fiddle.com

answered Dec 20 '19 at 08:44

Andreas

154,647
11
152
247

thanks for the response. Unfortunately I am not sure that this is possible with the default spring repository in an @query-notation so I don't think I can use the query like this. But it does solve the problem when executed directly on the database. – Juliette Dec 20 '19 at 08:56
1

@Juliette Really, I thought that the entire purpose of [`nativeQuery = true`](https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html#nativeQuery--) is that you can write anything you want that the database server understands. See: [Is it possible to use raw SQL within a Spring Repository](https://stackoverflow.com/q/15948795/5221149) – Andreas Dec 20 '19 at 09:06
uuuh I did not know that actually. Thanks a lot for the tipp, I will definetly try that! :) – Juliette Dec 20 '19 at 09:08

score 0 · Answer 2 · answered Dec 20 '19 at 08:42

0

I hope this helps :

    with cte1 as (
        select a.id, a.created_at, a.original_id
        from table_name a
    ),
    cte2 as (
        select max(b.created_at) as created_at, b.original_id
        from table_name b
        group by b.original_id
    )
    select c1.id, c2.created_at, c2.original_id
    from cte1 c1
    join cte2 c2 on c2.created_at = c1.created_at and c1.original_id = c2.original_id

answered Dec 20 '19 at 08:42

Dzejki

1
1

Hi :) thanks for the suggestion. Unfortunately I think even if this query works I won't be able to use it in a Java Spring Repository or @Query – Juliette Dec 20 '19 at 08:43
1

@Juliette Oh, sure. I'm glad you found the answer :) – Dzejki Dec 20 '19 at 08:45

Juliette · Answer 3 · 2019-12-20T10:19:23.417

-1

Disclaimer:This answer doesn't directly answer the question but suggests an alternative approach by changing the database schema

Thanks for all the suggestions, I just found a solution that is a little more elegant and can be more easily used with Java Spring (as far as I know sub-queries in the FROM-clause are not supported in @Query annotation)

It also solves the problem of two items having the same created_at date without creating a duplicate.

I simply changed the id-column from a UUID to an auto-incrementing int-column so that I can get my results simply with:

SELECT * FROM notes.test WHERE id IN (SELECT MAX(id) FROM notes.test GROUP BY parent_id)

edited Dec 20 '19 at 10:19

answered Dec 20 '19 at 08:38

Juliette

966
16
35

How can that be a solution when your requirement was *"have the largest `created_at`"* and the query doesn't even use the `created_at` column? --- Remember that your `id` columns are text, so `id` value `'a10'` is not greater than `id` value `'a9'`, which means that `MAX(id)` doesn't work. – Andreas Dec 20 '19 at 08:46
Also my requirement was "I want to know the ids of the newest history-entry for each element/for each original_id". I just thought that the way I described in my question was the best solution – Juliette Dec 20 '19 at 08:59
Your first sentence doesn't say that, and question didn't mention that modifying the schema was an option. The question was: *I have this schema, how to I write a query?* Which means that this not an answer to the question. – Andreas Dec 20 '19 at 08:59
I am sorry for not being clearer in my question. I tried to express that this concept was still in development but it seems that I failed in that aspect. Do you have any suggestions on how to edit my question? I would appreciate that – Juliette Dec 20 '19 at 09:05
The questions and answers on StackOverflow are supposed to be a repository of solutions to programming problems, allowing others to find already answered solutions. Nobody else will find a *"change the schema"* answer useful for a question saying *"I have this schema, how to I write a query?"*, especially when there are queries with working solutions. --- Anyway, I can't remove the down-vote without the answer being edited, so if you perhaps mention *up front* (first sentence), that this answer deviates by changing the schema, I'll remove the down-vote. – Andreas Dec 20 '19 at 09:14

SQL how to find ids of rows with largest created_date inside a grouped by

3 Answers3