CODEWITHSUNDEEP
sql-server
user1405195
user1405195

Reputation: 1681

Aggregate query grouping

The query below seems to work despite being wrong.

The Color column doesn't belong in an aggregate function but I don't want to group by Color. I want to return the color for the minimum priority grouped by Vehicle.

I hope the below is enough to work with - I was hoping for a quick answer but will go into more detail if necessary.

SELECT alert.VehicleID,
       min(hotcol.[Priority]) as [Priority]
       min(hotcol.Color) as Color,
FROM [ALERTS] alert
    INNER JOIN [HOTLISTS] hotlist ON alert.[HotlistID] = hotlist.[HotlistID]
    INNER JOIN [HOTLIST_COLORS] hotcol ON hotlist.ColorID = hotcol.ColorID
WHERE VehicleID = 17513851
GROUP BY alert.VehicleID

Upvotes: 2

Views: 63

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can use the ranking function ROW_NUMBER to do this. Something like this:

WITH CTE
AS
(
   SELECT 
     alert.VehicleID,
     hotcol.Color,
     hotcol.[Priority],
     ROW_NUMBER() OVER(PARTITION BY alert.VehicleID 
                       ORDER BY hotcol.[Priority]) AS RN
   FROM [ALERTS] alert
   INNER JOIN [HOTLISTS] hotlist ON alert.[HotlistID] = hotlist.[HotlistID]
   INNER JOIN [HOTLIST_COLORS] hotcol ON hotlist.ColorID = hotcol.ColorID
   WHERE VehicleID = 17513851
)
SELECT 
  VehicleID,
  Color,
  [Priority]
FROM CTE 
WHERE rn = 1;

The ROW_NUMBER function will give a ranking number for each alert.VehicleID and each group will be ordered by priority. Then WHERE rn = 1 will filter out all the rows except the minimum one which has rn = 1.

Upvotes: 1

Related Questions