CODEWITHSUNDEEP
sqlsql-servermin
akshay
akshay

Reputation: 777

I want to get minimum of starttime of each id. But I'm not able to achieve it using min function. How to solve it?

Table:

    id | starttime  | grade
    -------------------------
     1 | 4PM        | A
     1 | 5PM        | C
     2 | 2PM        | A
     2 | 3PM        | B

In output I should get all the ids that have minimum starttime with all the columns. For the above table output should be like this:

 id | starttime  | grade
-------------------------
  1 | 4PM        | A
  2 | 2PM        | A

Upvotes: 1

Views: 60

Answers (4)

user7715598
user7715598

Reputation: 

 ;WITH cte(id,starttime, grade)
 AS
 (
 SELECT 1 , '4PM','A' Union all
 SELECT 1 , '5PM','C' Union all
 SELECT 2 , '2PM','A' Union all
 SELECT 2 , '3PM','B'
 )

,Final AS (
    SELECT *
        ,Row_Number() OVER (
            PARTITION BY MinStartTime ORDER BY MinStartTime
            ) Seq
    FROM (
        SELECT id
            ,CAST(starttime AS TIME) AS starttime
            ,MIN(CAST(starttime AS TIME)) OVER (
                PARTITION BY ID ORDER BY starttime
                ) AS MinStartTime
            ,grade
        FROM cte
        ) Dt
    )

SELECT id
    ,CONVERT(VARCHAR(15), MinStartTime, 100) AS starttime
    ,grade
FROM final
WHERE Seq = 1
ORDER BY 1

Upvotes: 0

SqlZim
SqlZim

Reputation: 38053

Using top with ties and row_number() to get the minimum starttime for each id.

select top (1) with ties *
from t
order by row_number() over (partition by id order by starttime);

rextester demo: http://rextester.com/RJVT1405

returns: 

+----+-----------+-------+
| id | starttime | grade |
+----+-----------+-------+
|  1 | 4PM       | A     |
|  2 | 2PM       | A     |
+----+-----------+-------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270513

In SQL Server, use top with ties:

select top (1) with ties t.*
from t
order by starttime;

TOP (1) only returns one row. TOP (1) WITH TIES returns all rows that have the same key value as in the first row specified by the ORDER BY.

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23827

You can use group by:

with minTimes (id, startTime) as 
(
  select id, min(startTime) from myTable
  group by id
)
select t1.*
from myTable t1 
inner join minTimes t2 on t1.id = t2.id and t1.startTime = t2.startTime

Upvotes: 0

Related Questions