top 1 record of a group of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

top 1 record of a group of records

Author

Topic

rebelman
Starting Member

4 Posts

Posted - 2007-08-14 : 14:50:56

Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:

id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007

how would I get the data for the record with the newest date?
So I would return:

id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007

Thanks in advance for the help.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 14:59:09

select id, amount, max(date)
from table
group by id, amount

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 15:03:09

what about the amount, is the amount goiing to be different for 2 different dates.
my query will work only if the amount is also same. let me if you posted the data randomly and the amount is suppose to be same, if not my query is not correct

Ashley Rhodes

rebelman
Starting Member

4 Posts

Posted - 2007-08-14 : 15:03:43

I tried that and that seems to return all of the records, not just one of each group of id's.

Thanks
Mike

rebelman
Starting Member

4 Posts

Posted - 2007-08-14 : 15:04:36

The amounts are also random and different.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 15:06:19

[code]
Declare @T Table (id int, amount decimal(10,2), date datetime)
insert into @t
select 1, 100 ,'01/01/2006' union all
select 1, 2000, '06/01/2005' union all
select 2, 200, '01/01/2005' union all
select 2, 500 ,'06/01/2007'

select T.*
from @t T
JOIN ( SELECT Id, MAX(Date) AS MDate
FROM @T T2
GROUP BY ID
) T1 ON T.Id= T1.Id AND T.Date = T1.MDate
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 15:09:49

THIS WILL WORK FINE FOR YOUR SCENARIO IF AMOUNT IS NOT SAME

create table t111 (id int, amount int, date datetime)

insert into t111 values(1, 100, '01/01/2006')
insert into t111 values(1, 2000, '06/01/2005')
insert into t111 values(2, 200, '01/01/2005')
insert into t111 values(2, 500, '06/01/2007')

select A.id, A.Date, B.amount

from

(select id, max(date) Date from t111
group by id) A

inner join
(
select amount, max(date)date from t111 group by amount ) B

on A.date = B.date

Ashley Rhodes

rebelman
Starting Member

4 Posts

Posted - 2007-08-14 : 15:21:28

Thank you for the replies, they were very helpfull and solved my problem.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 16:49:03

which one was faster

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources