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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment