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
2
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
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
0
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
0
;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
2
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
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
0
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
0
;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