SQL GroupBy Select min return multiple columns

Question

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

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.

Answer 1

;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

Answer 2

So you need to get the minimum value and group by a column in a separate sub query, going by your small smaple of data it is safe to join back to the original table to pick up the other columns using ID & STP as these uniquely identify an individual row but if this is not true in the full data set you will have to take another approach. (sorry about the formatting)

SELECT Y.ID X.UID, X.MinSTP, Y.A, Y.B, Y.C ( SELECT UID, min(STP) as MinSTP FROM [drp-data].[dbo].[schedules] WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23') GROUP BY UID ) as X JOIN [drp-data].[dbo].[schedules] as Y ON X.UID = Y.UID and X.MinSTP = Y.STP

Answer 3

This seems to do it but I'm not sure if it's the most efficient way.

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