2

I have a database that contains duplicated names and timestamps I want to retrieve the record whose timestamp is the lowest grouped by the name.

table : people
+------------+-------------+
|  Name      |  Timestamp  |
+------------+-------------+
|   name1    |   0         |
|   name1    |   5         |
|   name2    |   2         |
|   name3    |   3         |
|   name2    |   1         |
+--------------------------+

With a database in this state the query should return "name2, 2" since the highest value for name2 is the lowest of the max value of all groups.

I've been thinking a lot about this since I know I've done similar queries but my SQL skills are too rusty.

Thanks to anyone that takes the time to help :)

GMB
  • 216,147
  • 25
  • 84
  • 135
Nacho R
  • 71
  • 1
  • 10

1 Answers1

2

It seems like you want the name whose greatest timestamp is the lowest: if so, you can use aggregation and limit:

select name
from people
group by name
order by max(timestamp)
limit 1

If you want to allow possible ties:

select name
from (
    select name, rank() over(order by max(timestamp)) rn
    from people
    group by name
) t
where rn = 1

On the other hand, if you want the entire record, I would recommend distinct on in Postgres:

select *
from (
    select distinct on (name) *
    from people
    order by name, timestamp desc
) t
order by timestamp
limit 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Looks like aggregation and limit is the way to go, I'm working on it but you've definately helped a lot, thank you so much – Nacho R Aug 30 '20 at 23:27
  • still dont know how to extract the whole record without using another query – Nacho R Aug 30 '20 at 23:36
  • 1
    @NachoR: I see your point. I edited the last query in my answer, it should do what you want. – GMB Aug 30 '20 at 23:39
  • 1
    This is just what I wanted, now I can perform all the queries I need to update records. Hope this also helps someone else too :) – Nacho R Aug 30 '20 at 23:47