select entry with the latest timestamp

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 entry with the latest timestamp

Author

Topic

sggy
Starting Member

8 Posts

Posted - 2007-07-18 : 17:34:39

Hi,

I have the following task:
table contains multiple records for same email address. i need to select a user with the latest createdate including his id

Table
-----
email id FN LN CreateDate
mail1 234 john stone 20070809
mail1 432 john stone 20060809
mail1 765 john stone 20050809

i need to select mail1 234 (email and id) since CreateDate is the latest for this account. I cannot figure out how to do this in one query. For now i
first group by mail and select max(createdate) into one table
and
then join two tables on mail and createdate and select id that corresponds to the latest createdate.

Do you know how to assomplish this in one query?

Thanks in advance,
sggy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 19:25:09

[code]SELECT *
FROM mail a
INNER JOIN
(
SELECT email, createdate = MAX(createdate)
FROM mail
GROUP BY email
) b
ON a.email = b.email
AND a.createdate = b.createdate[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-19 : 05:17:37

or

select columns from mail M where creadtedate=(select max(createdate) from mail where name=M.name)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources