How to get row which has max 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

General SQL Server Forums

New to SQL Server Programming

How to get row which has max timestamp

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2008-10-14 : 16:53:22

Hi All,

My query returns 3 records but i want one record where timestamp is greater than other one.( means has max timestamp value).

Sno FirstName LastName UserName timestamp
1 Fred Flinstone freddo 2007-05-02 07.33.25.798714
2 Homer Simpson homey 2007-05-02-13.42.50.378356
3 Homer Brown notsofamous 2007-05-02-13.36.55.468393

Can i use
ORDER BY TIMESTAMP MAX ?
Please Let me know if any one has suggestion.

Regards,
Shahid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-14 : 16:59:54

maybe:
select top 1
Sno,
Firstname,
...
from yourtable
where ...
order by timestamp DESC

Webfred

Planning replaces chance by mistake

shahid09
Starting Member

35 Posts

Posted - 2008-10-14 : 18:02:23

Thanks Webfred,

When i use Select top 1 i am getting an error. If i remove top 1 i get all three records and latest time first but when i use top 1 error is being generated.

Thanks,
Shahid

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-14 : 18:05:05

quote:
Originally posted by shahid09

Thanks Webfred,

When i use Select top 1 i am getting an error. If i remove top 1 i get all three records and latest time first but when i use top 1 error is being generated.

Thanks,
Shahid

Show us what you tried and the error as we can't see your system.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:07:21

If using sql 2005 then use this. this will even return you all records if you've more than existing for same max value

SELECT TOP (1) WITH TIES *
FROM
(Your Query)t
ORDER BY t.timestamp DESC

shahid09
Starting Member

35 Posts

Posted - 2008-10-15 : 11:41:47

select top 1
Sno,Firstname,
from table
where firstname = 'Homer'
order by timestamp DESC

And I got following error

An Expected token "1" was found following "" Expected token may include : "+ - as <Identifier> SQLState = 42601

When i remove top 1 i get two rows where firstname = Homer

Thanks,
Shahid

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:45:11

EXTRA "," at the end of "Firstname"

FIXED:


select top 1
Sno,Firstname
from table
where firstname = 'Homer'
order by timestamp DESC

shahid09
Starting Member

35 Posts

Posted - 2008-10-15 : 12:10:26

Sorry i typed it wrong here

It is actually

select top 1
Sno,Firstname
from table
where firstname = 'Homer'
order by timestamp DESC

But still getting same error.

Thanks,
Shahid

shahid09
Starting Member

35 Posts

Posted - 2008-10-15 : 12:24:56

One more thing we are using DB2 database

shahid09
Starting Member

35 Posts

Posted - 2008-10-15 : 12:36:06

I got it I think for DB2 we need to use

FETCH FIRST 1 ROWS ONLY

select Sno,Firstname
from table
where firstname = 'Homer'
order by timestamp DESC
FETCH FIRST 1 ROWS ONL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-15 : 12:42:37

quote:
Originally posted by shahid09

One more thing we are using DB2 database

You should always mention that you aren't using SQL Server when you are posting on a SQL Server site. It is very important that we know it's DB2.

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

Subscribe to my blog

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 13:59:26

quote:
Originally posted by shahid09

I got it I think for DB2 we need to use

FETCH FIRST 1 ROWS ONLY

I am also a regular on http://www.dbforums.com/forumdisplay.php?s=&daysprune=&f=8 The DB2 version of SQLTEAM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources