Need to select row_number which is last

Question

--------------------------------------------------------------
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 1

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;