|
Subject:
|
Trying to retrieve top 1 for multiple rows
|
|
Posted By:
|
MonicaM
|
Post Date:
|
5/15/2006 11:09:48 AM
|
Hi,
There may be a simple solution to my problem but it's got me stumped so hopefully someone can help.
I have a table that has multiple records for units .e.g.
Unit1 Location1 15/05/2006 10:00
Unit1 Location2 15/05/2006 11:00
Unit2 Location1 15/05/2006 12:00
Unit3 Location2 15/05/2006 13:00
Unit3 Location3 15/05/2006 14:00
I'm trying to retrieve the newest record for each unit .e.g.
Unit1 Location2 15/05/2006 11:00
Unit2 Location1 15/05/2006 12:00
Unit3 Location3 15/05/2006 14:00
The only way I’ve manage to pull back the correct data is using nested queries but it's too slow and resource intensive.
Can anyone suggest a query for me?
Thanks,
Monica
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
5/15/2006 11:20:23 AM
|
When you say 'nested queries' are you referring to a correlated subquery? If a correlated subquery is 'too slow', then you may not have the proper indexes in place.
Did you try:
SELECT *
FROM yourtable T1
WHERE yourdatetimecolumn
= (SELECT MAX(yourdatetimecolumn)
FROM yourtable T2 WHERE T1.UnitColumn = T2.UnitColumn)
This sort of subquery is about as fast as you can get, given appropriate indexes.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
|
Reply By:
|
MonicaM
|
Reply Date:
|
5/16/2006 3:29:22 AM
|
Hi Jeff,
Your solution is fantastic!! It's solved a problem I've worked around for ages so thanks very much.
Thanks again,
Monica
|
