SELECT DISTINCT problem - 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

SQL Server Development (2000)

SELECT DISTINCT problem

Author

Topic

teetime3
Starting Member

1 Post

Posted - 2010-02-05 : 17:40:32

I need to display multiple fields but need to restrict by the last entry for each individual.

SELECT SSAN, Location, Date_of_Trip
from TblTRIPS
Where
...only want to select the most recent trip

Need help with this. How can I restrict the select statement to the most recent trip for each individual. If I put DISTINCT in the SELECT clause I combines all the fields and give me all the trips for all the individuals...but I only want the most recent.

Thanks,
Al

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-05 : 18:01:26

You can use GROUP BY with the MAX aggregate function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 02:16:06

I suspect that you want the Person (SSAN??) and the Most Recent Trip - which is:


SELECT SSAN, MAX(Date_of_Trip) AS Most_Recent_Trip
from TblTRIPS
GROUP BY SSAN

but you also want the location OF that most recent trip, and that you could do something like:


SELECT SSAN, Location, Most_Recent_Trip
FROM TblTRIPS AS T
    JOIN
    (
        SELECT SSAN, MAX(Date_of_Trip) AS Most_Recent_Trip
        from TblTRIPS
        GROUP BY SSAN
    ) AS X
        ON X.SSAN = T.SSAN
       AND X.Most_Recent_Trip = T.Date_of_Trip

If a person made two trips on the same "most recent trip" date you will get two rows for them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources