Select TOP 1 of each - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select TOP 1 of each

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-08 : 11:31:09

OK, I feel a little dumb posting this since I've done it alot but I just have a severe brain cramp which could be due to being on vacation last week and going back on vacation in 2 hours. Anyway, here is the data...

Washer BOF_Date Weight
1 7/1/05 500
1 6/30/05 1000
2 7/1/05 1500
2 6/30/05 1000
3 7/1/05 800
3 6/30/05 1000

How do I get the latest record based on the BOF_Date for each washer?
1 7/1/05 500
2 7/1/05 1500
3 7/1/05 800

TIA

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-08 : 12:27:19

[code]SELECT
mt.Washer,
mt.BOF_Date,
mt.Weight
FROM
dbo.MyTable AS mt
JOIN
(
SELECT
mt.Washer,
MAX(mt.BOF_Date) AS BOF_Date
FROM
dbo.MyTable AS mt
GROUP BY
mt.Washer
) AS latest
ON mt.Washer = latest.Washer
AND mt.BOF_Date = latest.BOF_Date[/code]

Mark

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-08 : 12:35:17

Thanks Mark! I'm now off to kill some more brain cells!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-09 : 12:34:05

No problem

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-11 : 01:12:20

Try this also

select Distinct washer,(select max(BOF_Date) from yourTable where Washer=T.Washer
group by Washer ),Weight from yourTable t

Madhivanan

Failing to plan is Planning to fail

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-18 : 15:30:18

Madhivanan: That result set returned all 6 rows with a date of 7/1...

1 2005-07-01 00:00:00.000 500
1 2005-07-01 00:00:00.000 1000
2 2005-07-01 00:00:00.000 1000
2 2005-07-01 00:00:00.000 1500
3 2005-07-01 00:00:00.000 800
3 2005-07-01 00:00:00.000 1000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources