'LAST' is not a recognized function name.??

Question

hi..
i run
SELECT LAST([BookName]) FROM [Elegent].[dbo].[Book]
in sql server 2000 but it gives

'LAST' is not a recognized function name

how can i use last function in sql server

second question is that
how can i get second last value from any table...

Amit Choudhary · Answer

Hi Try below query: Modified the fields: Select [user_name] from (Select Row_number() over(Order by [user_id] Asc) as RowID  * from [users]) f Where RowID in (Select Count(*)-1 as n from [users]) This query will return the Second Last record for you from the table [users]. you can get nth from last by just replacing the value in sub query in Where clause Select Count(*)-4 as n from [users] etc. To get the Last record just remove the subtract expression from the subquery Select Count(*) as n from [users] And you are done :)

Vulpes · Answer

A great solution there by Amit :) Incidentally in the more usual case where the rows being returned are ordered in some way then you can find the last one by using reverse order and then taking the top one: SELECT TOP 1 FROM [BookName] FROM [Elegent].[dbo].[Book] ORDER BY [BookName] DESC However this simple approach is unable to get the second last row in isolation though it can get the 'TOP 2'.