none
Displaying a column not in the aggregate group by clause RRS feed

  • Question

  • I am looking at location sales and want to display each locations best sales day in units.  The table has every locations units sales per day. The code below displays a locations best day:

    SELECT LocationId
          ,MAX([TireUnitsNP]) as BestUnitDay
       
         
      FROM [work].[dbo].[BestUnitsDay]
     
      Group by LocationId
      order By BestUnitDay Desc

    But I also want to display the date that this best day occurred on.  THe field SalesDate is apart of the table but I don't want it as a part of the group by because I would then get more records per location.  Can anyone help me with a way to add the SalesDate to this query.

    Tuesday, November 19, 2019 4:44 PM

All replies

  • SELECT b2.LocationId, b2.BestUnitDay, b1.SalesDate
    FROM FROM [Work].[dbo].[BestUnitsDay] AS b1
    INNER JOIN
    (
    	SELECT LocationId, MAX([TireUnitsNP]) AS BestUnitDay
    	FROM [Work].[dbo].[BestUnitsDay]
    	GROUP BY LocationId
    ) AS b2 ON b2.LocationId = b1.LocationId AND b2.BestUnitDay = b1.[TireUnitsNP]
    ORDER BY b2.BestUnitDay DESC;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, November 19, 2019 4:53 PM
  • Or

    WITH CTE AS (
    	SELECT [LocationId], [TireUnitsNP], [SalesDate], ROW_NUMBER() OVER(PARTITION BY [LocationId] ORDER BY [TireUnitsNP] DESC) AS RowNumber
    	FROM [Work].[dbo].[BestUnitsDay]
    )
    
    SELECT [LocationId], [TireUnitsNP], [SalesDate]
    FROM CTE 
    WHERE RowNumber = 1;


    A Fan of SSIS, SSRS and SSAS


    Tuesday, November 19, 2019 4:57 PM
  • Thanks!!  Need to look into CTE more.
    Wednesday, November 20, 2019 6:13 PM
  • Or without CTE:

    SELECT [LocationId], [TireUnitsNP], [SalesDate]
    FROM (
    	SELECT [LocationId], [TireUnitsNP], [SalesDate], ROW_NUMBER() OVER(PARTITION BY [LocationId] ORDER BY [TireUnitsNP] DESC) AS RowNumber
    	FROM [Work].[dbo].[BestUnitsDay]
    ) AS t
    WHERE RowNumber = 1;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, November 20, 2019 6:59 PM