Question about using GROUP BY vs MAX() for a column that will only have one value

Question

Question about using GROUP BY vs MAX() for a column that will only have one value

diana.bodell 56517

Mr or Mrs. 500

Points: 599
More actions
January 15, 2019 at 10:06 am

#379022

If I have a query on a table that has a primary key and a description column and I am grouping by the primary key column to get an aggregation, is it better to include the description column in the GROUP BY or to use MAX(DESCRIPTION) in the SELECT statement? Obviously there is only one description per key column so it seems inefficient to group by it but want to understand the difference in how the engine would process it.
Example 1:
SELECT KEYCOLUMN,
   KEYDESCRIPTION,
   COUNT(*) AS RECS
FROM MYTABLE
GROUP BY KEYCOLUMN, KEYDESCRIPTION
Example 2:
SELECT KEYCOLUMN,
   MAX(KEYDESCRIPTION) AS KEYDESCRIPTION,
   COUNT(*) AS RECS
FROM MYTABLE
GROUP BY KEYCOLUMN
Obviously there is only one description per key column so it seems inefficient to group by it but want to understand the difference in how the engine would process it.
Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Why do you need either approach? What are you trying to do? If keyColumn is the PK, then any grouping on it will return 1 row and the count will always be 1. So why not just select it?

SELECT KEYCOLUMN, KEYDESCRIPTION FROM MYTABLE

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

It's usually more efficient to use MAX(), particularly for char columns. GROUP BY for an extra char column requires sorting/hashing that is typically much more overhead.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

diana.bodell 56517 Mr or Mrs. 500 Points: 599 More actions · Answer 3

Thanks, Scott. That answered my question. For Mike01, I should have used a better example: SUM(charges) or something like that.