SQL select the record whose HIGHEST timestamp is the LOWEST

user_11940119 0
Viewed 142

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 :)

greatest-n-per-group max sql-order-by postgresql sql
1 Answers

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
user_106767160
Related