HELP: sql statement assistance

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)

HELP: sql statement assistance

Author

Topic

paengski
Starting Member

2 Posts

Posted - 2011-04-06 : 03:47:56

Hi Guys! Given the following sample data:

TableName: PersonInfo
| Name | City | InsertDate |
===================
| Raf | mla | 04/02/2011
| Raf | qc | 01/01/1990
| Yan | pque | 04/01/2011
| Ron | pasig | 04/01/2011

SELECT DISTINCT(Name) FROM PersonInfo
// Display output will be
-> Raf
-> Yan
-> Ron

But when I try to used this:
SELECT DISTINCT(Name), City, InsertDate FROM PersonInfo
// It display all records

What I want to do is get the unique names (Raf, Yan and Ron) with their corresponding current City using the InsertDate.

These are the list of records that should display.
| Raf | mla | 04/02/2011
| Yan | pque | 04/01/2011
| Ron | pasig | 04/01/2011

Thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-06 : 04:01:46

select
pi.Name,
pi.City,
pi.InsertDate
from PersonInfo as pi
join
(select Name,max(InsertDate) as InsertDate from PersonInfo group by Name) as dt
on pi.Name = dt.Name and pi.InsertDate = dt.InsertDate

No, you're never too old to Yak'n'Roll if you're too young to die.

paengski
Starting Member

2 Posts

Posted - 2011-04-06 : 04:17:21

Thank you very much sir!

+1 for this!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources