CODEWITHSUNDEEP
sqlsql-server
Deboroh88
Deboroh88

Reputation: 401

SQL: MAX versus SELECT TOP 1

I'm not very expert with SQL, so pardon me for the newbie question. Also for my basic english.

I need to extract from a table the value of the row(s) with the maximum date. The problem is that I can only select some rows, and the conditions would consider also other tables, so to get the right rows I have to do some joins with these other tables.

Normally (using just one table), to resolve this problem I use an SQL statement like this one:

SELECT t1.value
FROM table t1
INNER JOIN (SELECT MAX(date) as maxDate
    FROM table
    WHERE field=3) t2
ON t1.date=t2.maxDate
WHERE t1.field=3

Now, in the case I am speaking of, I would need to do something like:

SELECT t1.value
FROM table t1
INNER JOIN otherTable o1 ON o1.key=t1.oKey
INNER JOIN (SELECT MAX(date) as maxDate
    FROM table t2
    INNER JOIN otherTable o2 ON o2.key=t2.oKey
    WHERE t2.field=3 and o2.oField=10) t3
ON t1.date=t3.maxDate
WHERE t1.field=3 and o1.oField=10

But this solution seems inefficient to me, because I have to "duplicate" all the joins, and the joins conditions.

Is there any other way to do this? I don't need to extract all the results (could be more than one), so I thought also at this solution:

SELECT TOP 1 t.value
FROM table t
INNER JOIN otherTable o ON o.key=t.oKey
WHERE t.field=3 and o.oField=10
ORDER BY t.date DESC

But I am not sure if the result will be correct: does it first sort the results and then select the top 1? In other words: can I be sure that the row is the one with the max date?

Also, is the second method actually more efficient?

Thank you guys!!

Upvotes: 1

Views: 11529

Answers (2)

theweeknd
theweeknd

Reputation: 287

To answer your question, select top 1 and max are not the same thing. Select top 1 will return you first row of result set, it doesn't matter what date/data is in that row.

If you use select top 1 date from table order by date desc you will get the max date, so no worry there.

Select max(date) will always give you the highest date, or to be precise the last date.

Upvotes: 1

sbiz
sbiz

Reputation: 321

The difference between MAX and TOP 1 was also discussed on these posts:

MAX vs Top 1 - which is better?

SQL performance MAX()

Upvotes: 2

Related Questions