Selecting unique items in SQL based on some of the properties

Question

I have a table with a lot of items that look like this:

{
    ID: xxx,
    number: 2001,
    timestamp: 2021-12-26T10:54:35.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},
{
    ID: xxx,
    number: 2001,
    timestamp: 2021-12-26T10:53:39.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},
{
    ID: xxx,
    number: 2002,
    timestamp: 2021-12-26T10:54:35.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},
{
    ID: xxx,
    number: 2002,
    timestamp: 2021-12-26T10:55:31.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},

What I want to do is to select all the items with a unique number and with the most recent timestamp. I need not just the number and the timestamp, but all properties of the items.

So the desired output is:

{
    ID: xxx,
    number: 2001,
    timestamp: 2021-12-26T10:54:35.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},
{
    ID: xxx,
    number: 2002,
    timestamp: 2021-12-26T10:55:31.000Z,
    latitude: xxx,
    longitude: yyy,
    -- and some more properties

},

I used this query: SELECT number, MAX(timestamp) FROM table GROUP BY number And it does select items with a unique number and the most recent timestamp, but this is where the problems start. I also need the longitude and latitude and all the other properties the item has, but if I try to select all of them, it will be necessary to use them in an aggregation function (which I don't want to use here) or group by, which I don't want to use either because then the entire database gets selected.

What's the proper way of doing it?

Most sql DBMSes support [window functions](https://en.wikipedia.org/wiki/Window_function_(SQL)). Tag the question with you DBMS — Serg, Dec 27 '21 at 09:52
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Stu, Dec 27 '21 at 10:35

score 1 · Accepted Answer · answered Dec 27 '21 at 10:33

1

A simple use of partition by

select *
from (
select row_number() over (partition by number order by timestampp desc) as ordering, timestampp, id, number, latitude, longitude
from tbl) x
where ordering = 1

answered Dec 27 '21 at 10:33

Sanjay Kumar

26
3

score 0 · Answer 2 · answered Dec 27 '21 at 09:55

0

select *
from (
select row_number() over (partition by timestamp, number order by timestamp desc) as ordering, id, number, latitude, longitude
from my_table)
where ordering = 1

answered Dec 27 '21 at 09:55

Oguen

505
7
21

Lucaele · Answer 3 · 2021-12-27T10:43:34.420

-1

You can use DISTINCT :

SELECT id, DISTINCT(number), * 
FROM yourtable
WHERE MAX(timestamp)

edited Dec 27 '21 at 10:43

answered Dec 27 '21 at 09:53

Lucaele

39
4

Selecting unique items in SQL based on some of the properties

3 Answers3