return top most recent data per email record

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)

return top most recent data per email record

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2004-06-23 : 18:25:39

I have a table with the following data in it, and I need to be able to return 1 row per Email, Source, but I need all of the data in the record, and it has to be the most recent entry based on the date. How can I do this? The following is what I am trying to get at:

Email Entry Source Flag
------------------------------------
d@hotmail.com 200402 1 C
d@hotmail.com 200302 2 B
d@hotmail.com 200312 3 X
e@hotmail.com 200312 1 A

Thanks for any help.

CREATE TABLE Emails
(Email char (50) ,
Entry char (6) ,
Source char (1) ,
Flag char (1)
)

INSERT Emails values ('d@hotmail.com','200401','1','A')
INSERT Emails values ('d@hotmail.com','200302','2','B')
INSERT Emails values ('d@hotmail.com','200402','1','C')
INSERT Emails values ('d@hotmail.com','200312','3','X')
INSERT Emails values ('d@hotmail.com','200311','3','Y')
INSERT Emails values ('a@hotmail.com','200312','1','A')
INSERT Emails values ('a@hotmail.com','200101','3','E')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-23 : 18:36:56

what is the key of this table? why aren't you using a datetime datatype? does source or flag have anything to do with the resultsets?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 18:40:52

[code]

SELECT e1.Email, e1.Entry, e1.Source, e1.Flag
FROM Emails e1
INNER JOIN
(
SELECT Email, MAX(Entry) AS Entry, Source
FROM Emails
GROUP BY Email, Source
) e2
ON e1.Email = e2.Email AND e1.Entry = e2.Entry AND e1.Source = e2.Source
ORDER BY e1.Email, e1.Source

[/code]

Your sample data doesn't match your expected result set, but I think the query should work for you.

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-23 : 18:41:34

I think you want:

SELECT a.email, a.entry, a.source, a.flag
   FROM emails AS a
   WHERE  a.entry = (SELECT Max(b.entry)
      FROM emails AS b
      WHERE  b.email = a.email
         AND b.source = a.source)
   ORDER BY a.email, a.entry, a.source

-PatP

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2004-06-23 : 19:11:23

Thanks for all your help, both are exactly what I needed. I made a typo on the table data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources