CODEWITHSUNDEEP
sqlsql-server
Yada
Yada

Reputation: 31265

SQL max row of a table

Which one is better in SQL Server 2008 or it doesn't matter as the database optimizes.

SELECT * FROM table
WHERE datecolumn = (SELECT max(datecolumn) FROM table);


SELECT TOP 1 * FROM table ORDER BY datecolumn DESC;

Upvotes: 5

Views: 1477

Answers (3)

Philip Fourie
Philip Fourie

Reputation: 117047

Looking at the actual SQL execution plan for these queries, it results in exactly the same execution plan and the same execution cost. However it might differ depending on the index setup and collected statistics, therefore it is best to do the check yourself.

One thing to be aware of is that the two queries might not return the same result. The 2nd SQL will always return one record where the first record might return multiple rows if datecolumn is not unique.

Upvotes: 9

tutorial-computer.com
tutorial-computer.com

Reputation: 86

SELECT * FROM table WHERE datecolumn = (SELECT max(datecolumn) FROM table);

SELECT TOP 1 * FROM table ORDER BY datecolumn DESC;

these query will return difference result in cost. from sql server execution plan the first query. the sub query will run first and scan the table tuples and get the max datecolumn after that the outer query will scan the table and search for datcolumn=..... this way is not efficiency.

second query is more efficiency because there is just one scan tuples of the table at one times.

for note: if the datacolumn is a primary key or index column, it is not significant difference of both query. but if this column not an index and primary key, it can significantly difference in a cost

Upvotes: 1

ceth
ceth

Reputation: 45335

You need to look at execution plan to the answer.

Upvotes: 0

Related Questions