Selecting unique items in SQL based on some of the properties-CodePudding

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?

CodePudding user response：

Instead of group by, since you want all the rows from your table, you can use the max window function and partition by number:

select id, number, max(timestamp) over (partition by number), latitude, longitude
from yourtable;

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

You can use DISTINCT :

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