Trying to retrieve top 1 for multiple rows

MonicaM · May 15th, 2006, 11:09 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

Jeff Mason · May 15th, 2006, 11:20 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:

Code:

    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

MonicaM · May 16th, 2006, 03:29 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