CODEWITHSUNDEEP
sqlsql-servert-sql
lucas
lucas

Reputation: 101

Max & Distinct based on several field

Based on the raw data below and expected result, I need help on how to come up with the correct query.

Basically, I need that data based on max(ID), however, do note that RATING and TYPE field could be different hence Group By wouldn't work.

Thank you.

Raw Data & Expected Result

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You seem to want the highest id for each NumId. You can do this using row_number():

select t.*
from (select t.*, row_number() over (partition by NumId order by id desc) as seqnum
      from t
     ) t
where t.seqnum = 1;

Upvotes: 2

jarlh
jarlh

Reputation: 44696

Use NOT EXISTS to return a row if no other row has the same NumTitle but a higher ID value:

select t1.*
from tablename t1
where not exists (select 1 from tablename t2
                  where t2.NumTitle = t1.NumTitle
                    and t2.ID > t1.ID)

Or, JOIN version:

select t1.*
from tablename t1
join (select NumTitle, MAX(ID) from tablename group by NumTitle) t2
    on t2.NumTitle = t1.NumTitle and t2.ID = t1.ID

Upvotes: 0

Related Questions