How to reproduce First/Last functions from Access to MS SQL Server
-
Hi,
I have data in this order
ID,Amount,Date
1, 1,01/01/2003
1, 4,11/11/2004--This is max Date for ID=1
1, 1,04/03/2002
2,32,02/02/2003
2,13,06/03/2005--This is max Date for ID=2
3,20,02/02/2004--This is max Date for ID=3
and I need the Amount for each ID where Date is max date for that ID, something like this
1, 4,11/11/2004
2,13,06/03/2005
3,20,02/02/2004
In Access this I can easly do by using order by date desc and First function, unfortunetly First Function is not supported in SQL Server.
any idea how to do this with query, I can use CURSOR and FETCH the records and for each of them figure out the amount for the max date but that's is not efficient soulution.
Thank you
-
The equivalent is
Select TOP 1 id,amount,date
Use that with your order by clause and you will be able to get the first and last rows.
-
Thanks for replay
If I'm right select top 1 id, amount, date will return only 1 record. I need 3 records to be returned. I need amount of the most recent date for each ID.
-
More than one way to skin that cat here.
select * from yourtable t1
where t1.[date]=
(select max([date]) from yourtable t2
where t1.id=t2.id)
order by t1.id
select t2.id, t1.amount, t2.maxdate from yourtable t1 inner join
(select id, max([date]) as maxdate from yourtable group by id) t2
on t1.id = t2.id
and t1.[date] = t2.maxdate
order by t1.id
select t1.*
from yourtable t1
where t1.[date] in
(
select top 1 t2.[date]
from yourtable t2
where t2.id = t1.id
order by t2.[date] desc
)
order by t1.id
should all produce the result you want.
Who needs FIRST() and LAST() anyway?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank
Thank you very much!
That was just exactly what I was looking for. I new there is simple solution and u've show it to me.
Thanks
Bose
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply