Red Devil Red Devil - 2 months ago 11
SQL Question

Need to select row_number which is last

--------------------------------------------------------------
AnimalName AnimalType RowNumber
---------------------------------------------------------------
Chicken Bird 1
Duck Bird 2
Alpaca Mammal 1
Camel Mammal 2
Carabao Mammal 3
Whale Sea 1
Shark sea 2
Prawns Sea 3
Eel sea 4


OUTPUT

AnimalName AnimalType RowNumber
------------------------------------------------------------
Duck Bird 2
Carabao Mammal 3
Eel sea 4


Query:

SELECT t.* from (
select
AnimalName,
AnimalType,
ROW_NUMBER() OVER(PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber
FROM Animal A
) t
where rownumber=1


Above query is giving me all the 1st rownumber, If i want to select the last rownumber then what changes i have to make.
for eg: bird max rownumber is 2, mammal max is 3 and sea max is 4

Answer

Just use DESC instead of ASC for the ORDER BY:

SELECT a.* 
FROM (SELECT AnimalName, AnimalType,
             ROW_NUMBER() OVER (PARTITION BY AnimalType ORDER BY AnimalName DESC) AS RowNumber
      FROM Animal a
     ) a
WHERE rownumber = 1;

If you really want to keep the row numbers, the same, then use COUNT(*) for the comparison:

SELECT a.AnimalName, a.AnimalType, a.rownumber
FROM (SELECT AnimalName, AnimalType,
             ROW_NUMBER() OVER (PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber,
             COUNT(*) OVER (PARTITION BY AnimalType) as cnt
      FROM Animal a
     ) a
WHERE rownumber = cnt;
Comments