row_number function 1

[lasd]

Hi
I have a bit of a problem. Below I have pasted the row_number function i am using in my code. However i want to select where RowNumber=1 in my where clause but it doesn't recognise it. could anyone help here.

ROW_NUMBER() OVER (PARTITION BY item_key ORDER BY execution_time desc)as RowNumber

this is what i tried but it won't work.
where RowNumber=1

thanks in advance for the help.

lasd

 

[JarlH]

Column labels (i.e. RowNumber) are only used for ORDER BY purposes and re-naming output, you can not use it in the WHERE-clause.

 

[juliane26]

Still - most DBMS have features for fetching only the first row or first set of rows of a resultset, so if that would solve your issue post in the DBMS specific forum again.



Juliane

 

[swampBoogie]

select rowNumber, 
otherColumns 
from 
(select ROW_NUMBER() OVER 
(PARTITION BY item_key 
 ORDER BY execution_time desc) as RowNumber,
 otherColumns 
from ... )dt
where rowNumber = 1