SQL: MAX versus SELECT TOP 1

Question

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!!

SELECT statement is always executed at end of query so yes if you do a select top you will get the first one after sorting — Jeremy C., Jun 08 '15 at 07:59

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

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

MAX vs Top 1 - which is better?

SQL performance MAX()

edited May 23 '17 at 10:30

Community

1
1

answered Jun 08 '15 at 08:47

sbiz

321
3
10

The second link is more detailed, now I'm performing some checks as that reply suggested. Thank you for your help! – Deboroh88 Jun 08 '15 at 09:22

score 0 · Answer 2 · edited Jun 08 '15 at 09:30

0

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.

edited Jun 08 '15 at 09:30

Amnesh Goel

2,617
3
28
47

answered Jun 08 '15 at 08:08

theweeknd

277
1
12

thank you for your reply! Although, my title was not very specific for my real question, sorry. Anyway, can you also tell me which one solution is more efficient? even with a huge amount of rows? I fear that the order by, with many rows, could be too slow. – Deboroh88 Jun 08 '15 at 08:18
it is explained in detail in the links that @sbiz posted, just check it out ;) – theweeknd Jun 08 '15 at 09:12

SQL: MAX versus SELECT TOP 1

2 Answers2