How to get distinct rows based on the distinct(column) selected using SQL Server

Question

It could be pretty simple but I searched and attempted for past day but my solution does not work.

My data_table looks like this:

SprintID, Start_Date,                  End_Date
--------------------------------------------------------------
S100      2019-01-01 08:00:16.793      2019-01-10 06:59:00.000
S101      2019-01-11 08:00:19.793      2019-01-20 06:59:00.000
S101      2019-01-11 09:00:16.793      2019-01-20 07:14:00.000
S101      2019-01-11 11:00:16.793      2019-01-20 08:32:00.000
S102      2019-01-21 08:00:16.793      2019-01-30 09:19:00.000
S102      2019-01-21 09:45:16.793      2019-01-30 06:59:00.000
...

Notice that in the above data we can see that the Start_Date and End_Date has the same day but different time

What do I want?

What I am looking for is a single/distinct value of SprintID with corresponding Start_Date and End_Date

SprintID, Start_Date,                  End_Date
--------------------------------------------------------------
S100      2019-01-01 08:00:16.793      2019-01-10 06:59:00.000
S101      2019-01-11 08:00:19.793      2019-01-20 06:59:00.000
S102      2019-01-21 08:00:16.793      2019-01-30 09:19:00.000
...

What did I do?

I used the following code but it does not do what I am excepting:

Method 1

SELECT DISTINCT(SprintID), Start_Date, End_Date
FROM data_table

Method 2

SELECT SprintID, Start_Date, End_Date
FROM data_table
GROUP BY SprintID, Start_Date, End_Date
ORDER BY SprintID, Start_Date, End_Date

question from:https://stackoverflow.com/questions/65645886/how-to-get-distinct-rows-based-on-the-distinctcolumn-selected-using-sql-server

Answer 1

You can use window functions:

select d.*
from (select d.*,
             row_number() over (partition by sprintid order by start_date) as seqnum
      from data_table dt
    ) d
where seqnum = 1;

The above returns a row with the earliest start_date. However, you can adjust the order by to control what row you want. The expression order by newid() returns a random row.