selecting row with the lowest created_date

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)

selecting row with the lowest created_date

Author

Topic

adcworks
Starting Member

3 Posts

Posted - 2004-02-10 : 04:34:17

Hi guys

I have a table T defined with 2 columns

rid | created_date
--------------------
0 | 9/2/2004 12:00
1 | 9/2/2004 12:05

I need an tsql query that will return me the rid 0 because it has the earliest datetime in created_date

I've tried using MIN(CONVERT(INT, created_date)) but not with any success. This should work for lots of rows and one cannot rely on the rid being sequential.

Thanks!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-10 : 04:44:35

Use the TOP keyword while sorting by the created_date:

SELECT TOP 1 rid FROM T ORDER BY created_date ASC

OS

mr_mist
Grunnio

1870 Posts

Posted - 2004-02-10 : 04:46:06

What's wrong with min(created_date) ?

-------
Moo. :)

adcworks
Starting Member

3 Posts

Posted - 2004-02-10 : 04:49:39

min would not work on created_date without a cast/convert. I am also a little rusty at tsql and did not know how to construct a query for saying get me the rid with the earliest date.

i will try top but looks good, cheers

mr_mist
Grunnio

1870 Posts

Posted - 2004-02-10 : 05:21:07

It should be something like

SELECT rid FROM T where created_date = (SELECT min(created_date) from t)

There's no reason why the MIN function should not work on a date.

-------
Moo. :)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-02-10 : 05:34:54

It didnt work because you converted the date to an integer and as your sample data had 2 dates the same the results of the convert are the same

Example
SELECT CONVERT(int,GETDATE())
SELECT CONVERT(int,DATEADD(mi,5,GETDATE())) --Add 5 minutes

Both return the same value - 38025

Mr Mist is correct there is no reason why MIN function should not work on a date, just dont convert it

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-10 : 07:17:09

What if the earliest created_date is found in more than one row with different RIDs? You need to elaborate on your requirements.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources