Selecting the "max" record from a table

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 the "max" record from a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-20 : 08:19:30

Mike writes "I often need to select the record that is "greater than" all other records in a table, based on some field (usually a date field, to get the most recent, e.g.) I know of at least 3 ways to do this:


SELECT *
FROM my_table
WHERE my_date >= ALL (SELECT my_date
                      FROM my_table)

SELECT *
FROM my_table
WHERE my_date = (SELECT MAX(my_date)
                 FROM my_table)

SELECT *
FROM my_table
WHERE my_date = (SELECT TOP 1 my_date
                 FROM my_table
                 ORDER BY my_date DESC)

Which is better and why? Or is there another superior way? Thanks!"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-20 : 08:25:25

also the correlated sub-query


select *
from my_table t
where not exists (
    select *
    from my_table
    where t.my_date < my_date)

I don't believe SQL Server supports the ALL keyword.

I think in many cases your second solution (the aggregated subquery) will be best, but it will depend on indexing.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-20 : 11:59:21

Also if you just want one record (also works for multiple ordering fields)

SELECT TOP 1 *
FROM my_table
ORDER BY my_date DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources