Monday, March 12, 2012

Last Aggregate function

Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.
How to replace this functionality?
Thanks.Hi

You can get this using row_number with an OVER clause in 2005. Check it out in BoL and see how you get on. HINT - you will need to order descending.

HTH|||Thanks for the quick response. I however am not too sure how to do it.

Let's say I have a query like this:

select
last(firstname),
lastname

from
users

group by
lastname

but the last() function does not work, so how to do it using your method?

I am quite new to the SQL so sorry for asking obvious things maybe :-)|||Ok, I think the answer is:

with A as (
select
row_number()
over (partition by lastname order by firstname desc) as 'row'
firstname
lastname
from
users
)
select
firstname
lastname
from
A
where
row = 1|||Now I am not sure that returns what you want. Does is return the same as this?
SELECT lastname
, MAX(firstname) AS firstname
FROM dbo.A
GROUP BY lastname

No comments:

Post a Comment