1

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.

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

| improve this answer | |
0

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

| improve this answer | |
  • Hi, thanks for the reply. That doesn't seem to work as there is a syntax error on the second SELECT. The table will end up having about 400m rows in it. – Liam Sep 25 at 19:49
  • -- I missed the FROM sorry SELECT Y.ID X.UID, X.MinSTP, Y.A, Y.B, Y.C /**/ FROM /**/( 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 – Mo64 Sep 27 at 9:17
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.