Quick one - 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

General SQL Server Forums

New to SQL Server Programming

Quick one

Author

Topic

Gaillimh
Starting Member

3 Posts

Posted - 2012-04-13 : 13:42:03

Here's my table:


date                     disabled
2012-01-01 00:04:00      1
2012-01-01 00:03:00      0
2012-01-01 00:02:00      0
2012-01-01 00:01:00      1

I can retrieve the oldest record with


SELECT * FROM table WHERE date = (SELECT min(date) FROM table)

I now want to retrieve the oldest record where disabled = 0


SELECT * FROM table WHERE date = (SELECT min(date) FROM table) AND disabled = 0

This returns nothing. Any ideas? It should return


date                     disabled
2012-01-01 00:02:00      0

Many thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 13:45:05

[code]SELECT * FROM table WHERE date =
(SELECT min(date) FROM table WHERE disabled = 0)[/code]

Gaillimh
Starting Member

3 Posts

Posted - 2012-04-13 : 13:48:43

Perfect. Thank you. Have a great weekend.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-13 : 14:12:12

If you're only expecting one row, use this instead:

SELECT TOP 1 *
FROM table
WHERE disabled = 0
ORDER BY date

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

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 14:38:33

Using TOP would be simpler and probably more efficient. If there can be multiple rows with the same timestamp and you want to get all of them, use "TOP 1 WITH TIES" instead of "TOP 1" in Tara's query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources