Selecting Distinct Records - 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)

Selecting Distinct Records

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-09-11 : 06:21:16

Hi All,

I thought this would be a very easy query but it does not seem to be working.

I have a table (EID, Name, cDate)
and the records
42, Gavin, 01/01/2000
43, Elaine, 02/01/2000
42, Gavin, 02/01/2000

I need to run a query that will give me the distinct values of EID and the latest cDate value.

I tried
SELECT DISTINCT(EID), Name, cDate
FROM Compliments

but it brings back all three records?

If at all possible I would also like to get a count on the distinct records ie. Gavin(2), Elaine(1)

Thanks,
Gavin

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-11 : 06:58:49

[code]
SELECT EID, [Name], MAX(cDate), COUNT(*)
FROM yourTable
GROUP BY EID, [Name]
[/code]

__________________
Make love not war!

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-09-11 : 07:22:09

I had an answer but your's is so much simpler :)....

Thanks a ton,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources