50

I had to review some code, and came across something that someone did, and can't think of a reason why my way is better and it probably isn't, so, which is better/safer/more efficient?

SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5 GROUP BY event_id

OR

SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date

I would have gone with the 2nd option, but I'm not sure why, and if that's right.

Andreea
  • 105
  • 6
Craig
  • 18,074
  • 38
  • 147
  • 248
  • 2
    If `a_primary_key` truly is the table's primary key column, then there is no point having a query that mentions either `TOP 1 .. ORDER BY` or `MAX .. GROUP BY`. A primary key uniquely identifies a single row; there is no point ordering or maxing a single row, just say `SELECT a_date FROM a_table WHERE a_primary_key = 5`; you'll get between 0 and 1 results regardless – Caius Jard Oct 26 '20 at 13:38

8 Answers8

50

  1. When there is a clustered index on the table and the column to be queried, both the MAX() operator and the query SELECT TOP 1 will have almost identical performance.

  2. When there is no clustered index on the table and the column to be queried, the MAX() operator offers the better performance.

Reference: http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Junior Mayhé
  • 16,144
  • 26
  • 115
  • 161
24

Performance is generally similar, if your table is indexed.

Worth considering though: Top usually only makes sense if you're ordering your results (otherwise, top of what?)

Ordering a result requires more processing.

Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)

In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.

Chains
  • 12,541
  • 8
  • 45
  • 62
  • 1
    SQL Server TOP 1 behaves differently than Oracle using rowNum=1. Oracle actually does grab the first it finds BEFORE the ordering, so this method is valid only for SQL Server. Another benefit of this TOP 1 vs Max() is that you can grab as many columns as you want, as long as you include applicable ordering. I tested using Max() and found even with GROUP BY that it didn't seem to yield only 1 record. Perhaps someone as stronger mojo can say how to get just one row if you want to get the top from multiple columns w/o a sub-query? – gordon Aug 08 '13 at 13:47
  • 3
    The reason the 'max' code above is returning multiple entries is because of the group buy. It is returning a MAX for each Event Id. So it actually has different semantics to the 'top' example. – MultiMat May 22 '18 at 16:17
13

They are different queries.

The first one returns many records (the biggest a_date for each event_id found within a_primary_key = 5)

The second one returns one record (the smallest a_date found within a_primary_key = 5).

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 2
    o.O the first query will still return one record – Nick Rolando Aug 25 '11 at 23:23
  • 2
    @Shredder Provided `a_primary_key` is actually a primary key, it will. But if it is a primary key, you only have one date in `a_date`, and you need neither `max` nor `top`. – GSerg Aug 25 '11 at 23:28
  • Even if it wasn't, it will still return one record. http://www.w3schools.com/sql/sql_func_max.asp – Nick Rolando Aug 25 '11 at 23:34
  • 2
    @Shredder Can you see that the first query has `GROUP BY`, or are you assuming that `ORDER BY` in the second query means there's also `ORDER BY` in the first one? – GSerg Aug 25 '11 at 23:37
  • 4
    @Shredder you might be tarred and feathered for posting a link to w3schools. :) – Taryn Aug 25 '11 at 23:37
  • @GSerg Ok, you got me there. I didn't see the group by clause @-bluefeet hahahaah – Nick Rolando Aug 25 '11 at 23:43
  • Top returns all records with max column value. Max(column) returns only 1 value. – user2330678 Oct 20 '15 at 19:16
  • `Top` doesn't return all records, unless it's `with ties` @user2330678. – GSerg Oct 20 '15 at 20:14
  • My bad I used w3schools instead of sql fiddle for testing. – user2330678 Oct 20 '15 at 21:40
  • @GSerg The two queries are different with group by is that right? So the queries in question are different but they would be the same with/without group by on both. Is that right? – user2330678 Oct 20 '15 at 21:45
  • @user2330678 Considering that we only select one field, yes, they would be logically the same. In practice you often find that in a complex query one or another performs much faster. – GSerg Oct 20 '15 at 22:18
6

For the queries to have the same result you would need:

SELECT MAX(a_date) FROM a_table WHERE a_primary_key = 5

SELECT TOP 1 a_date FROM a_table WHERE a_primary_key = 5 ORDER BY a_date DESC

The best way to know which is faster is to check the query plan and do your benchmarks. There are many factors that would affect the speed, such as table/heap size, etc. And even different versions of the same database may be optimized to favor one query over the other.

vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • 4
    No need to group by in the first example (because you've only got one group, per your WHERE clause). – Chains Aug 25 '11 at 23:55
  • @kuru: I'm not sure you can use an aggregate function w/o a group by, but if so... you're right – vol7ron Aug 26 '11 at 00:46
  • 3
    As long as you're only selecting aggs (as you are in your answer), you're ok. If you include a non-agg value in the select, then you do need a group by. – Chains Aug 26 '11 at 01:13
  • Thanks for the follow-up, that was my initial inclination, but I'm going to take your word for it and update the answer, +1 cheers – vol7ron Aug 26 '11 at 01:15
3

I perform max and top on one table with 20,00,000+ records , and found that Top give faster result with order by than max or min function.

So , best way is to execute both your query one by one for some time and check connection elapsed time for than.

Jay Magwadiya
  • 410
  • 2
  • 6
  • 14
2

To add the otherwise brilliant responses noting that the queries do very different things indeed, I'd like to point out that the results will be very different if there are no rows matching the criteria in the select.

  • SELECT MAX() will return one result with a NULL value
  • SELECT TOP 1 will result zero results

These are very different things.

villenator
  • 21
  • 2
1

MAX and TOP function differently. Your first query will return the maximum value found for a_date that has a a_primary_key = 5 for each different event_id found. The second query will simply grab the first a_date with a a_primary_key = 5 found in the result set.

Nick Rolando
  • 25,879
  • 13
  • 79
  • 119
  • 2
    It won't grab the first value. `Top`, when combined with `order by asc`, will select the smallest value. – GSerg Aug 25 '11 at 23:29
  • 1
    wth are you smokin bro, gimme some of that.. It *will* grab the first value found, and yes, in this case it will be the smallest since ordering by ASC goes from smallest to biggest, making the first value the smallest.. – Nick Rolando Aug 25 '11 at 23:32
-1

I ran an experiment and I got the Clustered Index Cost 98% when I used an aggregate like Min/ Max, but when I used TOP and Order By, Clustered Index Scan cost was reduced to 45%. When it comes to querying large datasets, TOP and Order By combination will be less expensive and will give faster results.

Ezio
  • 376
  • 5
  • 21