Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MAX vs Top 1 - which is better?

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.

like image 306
Craig Avatar asked Aug 25 '11 23:08

Craig


People also ask

What is select Max?

Select Max® Herbicide with Inside Technology™ With adjuvant and tank mix flexibility, Select Max is a postemergence herbicide that offers total performance including the fastest, most powerful way to control volunteer corn and other grasses in many row and specialty crops.

How do you find the difference between minimum and maximum values in SQL?

SELECT date(`date`), (max(value) - min(value)) as value, FROM `sub_meter_data` where date(sub_meter_data. date) > '2012-10-01' and sub_meterID in('58984','58985','58986','58987') group by date(`date`);


2 Answers

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/

like image 153
Junior Mayhé Avatar answered Sep 19 '22 13:09

Junior Mayhé


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.

like image 23
Chains Avatar answered Sep 22 '22 13:09

Chains