[Solved] Select top 1 from each group sql

by


Maybe this… get the maximum sum for each city and menu item name. Top returns 1 row not one row per group. you need to use the max aggregate to make this work the way you want.

you can’t double aggregate max(sum(Quantity)) so you have to either use a sub select, or use a CTE (common table expression). This is the subselect.

Select city, itemName, max(Quantity)
FROM (
            SELECT 
                    l.city AS City,
                    mi.name AS ItemName,
                    SUM(ft.quantity_sold) AS Quantity
            FROM
                    FactTable ft
                            JOIN MenuItem mi ON (ft.menuItemID = mi.ID)
                            JOIN Location l ON (ft.locationID = l.ID)
            GROUP BY
                    l.city, mi.name) sub
GROUP BY City, ItemName;

0

solved Select top 1 from each group sql