How do I select 1 [oldest] row per group of rows, given multiple groups?-CodePudding

Let's say we have the database table below, called USER_JOBS.

I'd like to write an SQL query that reflects this algorithm:

Divide the whole table in groups of rows defined by a common USER_ID (in the example table, the 2 resulting groups are colored yellow & green)
From each group, select the oldest row (according to SCHEDULE_TIME)

From this example table, the desired SQL query would return these 2 rows:

CodePudding user response：

You can use ranking function (supported in most RDBS):

SELECT *
FROM
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY SCHEDULE_TIME DESC) AS RowID
    FROM [table]
)
WHERE RowID = 1

CodePudding user response：

    WITH Ranked AS (
        SELECT 
            RANK() OVER (PARTITION BY User_ID ORDER BY ScheduleTime DESC) as Ranking,
            *
        FROM [table_name]
    )

    SELECT Status, Sob_Type, User_ID, TimeStamp FROM ranking WHERE Ranks = 1;