我想获得每个id的最小启动时间。但我无法使用min函数实现它。怎么解决？

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。对于上面的表，输出应该是这样的：

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

4 个解决方案

#1

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

使用top with tie和row_number（）来获取每个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

rextester演示：http：//rextester.com/RJVT1405

returns:

收益：

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

#2

In SQL Server, use top with ties:

在SQL Server中，使用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.

TOP（1）只返回一行。 TOP（1）WITH TIES返回与ORDER BY指定的第一行具有相同键值的所有行。

#3

You can use group by:

您可以使用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

#4

 ;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

#1