CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Brie
Brie

Reputation: 2359

SQL group by 1 column but include TOP 1 of other columns

I am trying to build a SQL query where I group by 1 column, but then also include the values of other columns from an arbitrary record in each group. So, something like

SELECT BoxNo 
FROM MuffinData 
WHERE FrostingTimeApplied >= CONVERT(date, GETDATE()) 
GROUP BY BoxNo

but including some value from columns MuffinType, FrostingType in the result (I know that there will be only 1 value of MuffinType and FrostingType per box.)

Upvotes: 0

Views: 210

Answers (4)

Itzik Ben-Gan
Itzik Ben-Gan

Reputation: 386

I know that there will be only 1 value of MuffinType and FrostingType per box

If that's indeed the case, a simple DISTINCT should do the trick, like so:

SELECT DISTINCT BoxNo, MuffinType, FrostingType
FROM MuffinData 
WHERE FrostingTimeApplied >= CONVERT(date, GETDATE());

If that's not the case, you're dealing with a problem known generally as the Top N per group problem. You can find coverage of the problem and suggested solutions here.

Cheers, Itzik

Upvotes: 1

RobertoGuzman
RobertoGuzman

Reputation: 75

You can use a CTE and join back to the original table to get the fields you want. In this case,

WITH BoxGroup AS (SELECT BoxNo FROM MuffinData WHERE FrostingTimeApplied >= CONVERT(date, GETDATE()) GROUP BY BoxNo) SELECT md.BoxNo,md.MuffinType,md.FrostingType FROM MuffinData md INNER JOIN BoxGroup bg ON bg.BoxNo = md.BoxNo

Upvotes: 0

user4650451
user4650451

Reputation:

If you're grouping by anything, then the only way to do this in a single statement (that I'm aware of) is to have the other columns you're returning be the result of an aggregate function. Aggregate functions are anything that take multiple values but return you a single result like: SUM, MAX, MIN, COUNT, etc...

SELECT BoxNo, COUNT(MuffinData.ID), MAX(FrostingType.FlavorID) FROM MuffinData, FrostingType etc...

You might have to adjust your WHERE logic or have another data source in your FROM list (subquery).

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You have to use an aggregate function for each column selected that is not present in the GROUP BY clause:

SELECT BoxNo, MAX(MuffinType) AS  MuffinType, MAX(FrostingType) AS FrostingType
FROM MuffinData 
WHERE FrostingTimeApplied >= CONVERT(date, GETDATE()) 
GROUP BY BoxNo

If there is only 1 value of MuffinType and FrostingType per box, then these unique values per box no are going to be selected in the above query.

Upvotes: 1

Related Questions