Using Row_number() function

[karthick ganeshraman]

Hi,

Im trying to use the row_number() SQL function to partition a table using a particular column, ordering it in descending and pick the first row as the resultant. Below is the query i used

Select * from
(
(Select Col_name1, Col_name2,
Row_number() over (Partition by Col_name3 order by Col_name4 desc)r
from table1)
) where r = 1

But i get the SQL error 42000 (Systax error)

Is there anything that needs to be amended in the syntax ? The same query with the above syntax works correctly in Oracle toad
or
Is there any plugin that need to be added in order for the above function to work ?

Kindly help

[smcleod]

[karthick ganeshraman]

Hi,

Thanks for the Info.

I tried the way that you have suggested by using the inner select.

But the result is still the same. I get the 42000 error

Thanks