Search code examples
sqlpostgresqlgreatest-n-per-group

Get unique values by multi column

I got that table

CREATE TABLE test (
    P_IP varchar(255),
    MAC varchar(255),
    R_IP varchar(255),
    dates TIMESTAMP
);

Every column can be duplicate. Each row is report by timestamp I wanna get the latest row unique mac by timestamp. and if there duplicate by the other column let it take one of them, I dont care which

Example: that is my table :

Blockquote

the rows I looking for is the green rows

  1. the first green row(row num' 2) is only have 1 Mac report so it should take it
  2. the second green row(row num' 4) is taken cause it's timestamp is bigger then other row with the same Mac
  3. the third green row (row num' 5) is taken cause I wanna take the first Row that have the same mac and same timestamp.

So I tried this:

select * from test as our left JOIN (
select MAC as mac,MAX(rip) as rip,max(dates) as dates from test
group by mac) as temp ON
our.mac = temp.mac AND
our.rip = temp.rip AND
our.dates = temp.dates

but it do max on the the timestamp and max on the r_ip that create a row that not really exist. Is there a way to get what I needed

Solution

  • This is achievable using windows function.

    select t.P_IP, t.R_IP, t.Mac, t.dates from (
    select row_number() over (partition by mac order by dates desc
      ,r_ip asc) rn_mac, * from test) t
where t.rn_mac = 1

    see this dbfiddle