Sql-server – SQL GroupBy Select min return multiple columns

group bysql server

I have the following sample of a SQL table, which will be filled with around 400 million rows:

enter image description here

I want to be able to return columns Id, UID, STP, A, B, C and then group by the UID and keep the one with the lowest STP values, where a date is in between the StartDate and EndDate (i.e. 23/05/2011)

This is what I have so far:

SELECT Id, UID, STP, A, B, C
FROM [drp-data].[dbo].[schedules]
WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')

Microsoft SQL 2019.

Best Answer

;WITH CTE AS 
    (
    SELECT Id, UID, STP, A, B ,C
        ,ROW_NUMBER() OVER (PARTITION BY UID ORDER BY STP) AS rn
    FROM [drp-data].[dbo].[schedules]
    WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')
    )
    SELECT  Id, UID, STP, A, B ,C
    FROM CTE 
    WHERE rn = 1

please see this post too: Retrieving n rows per group

Related Question