为每组选择具有最小值的行

Question

我有一个数据库，其中有一个表如下所示：

容器ID<td+1>

日期
1/1
2	1
3	1
4/td>	2
5/5	2
6/6	3
7/7	3

但最好将日期保存为日期列。您可以使用日期的所有功能

CREATE TABLE table1 (
[Date]  varchar(10),
[Container ID] INTEGER
);
INSERT INTO table1
([Date], [Container ID])
VALUES
('1/1', '1'),
('2/2', '1'),
('3/3', '1'),
('4/4', '2'),
('5/5', '2'),
('6/6', '3'),
('7/7', '3');
GO

SELECT MIN([Date]), [Container ID] FROM table1 GROUP BY [Container ID]
GO

(无列名(|容器ID：---------------|---------------：1/14/4|26/6|3

db<gt；小提琴这里

只是使用WITH TIES的另一个选项

Select top 1 with ties *
From  YourTable
Order By row_number() over (partition by ContainerID Order by Date)

您想要group by和min():

select id, min(date)
from t
group by id;

注意：您也可以使用join，但它有点晦涩：

select t.*
from t left join
t t2
on t.id = t2.id and t2.date < t.date
where t2.id is null;

left join与日期较小的行匹配。最小的日期(每个id(是没有匹配行的日期。

我没有使用日期列，但您可以根据调整您的日期列

CREATE TABLE DAta (Date_example VARCHAR(50), ContainerID INT)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1a', 1)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2a', 1)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3a', 1)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1b', 2)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2b', 2)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3b', 2)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1c', 3)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2c', 3)
INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3c', 3)

生成的表格为：

+---------------+-------------+
| Date_example  | ContainerID |
+---------------+-------------+
| 1a            |           1 |
| 2a            |           1 |
| 3a            |           1 |
| 1b            |           2 |
| 2b            |           2 |
| 3b            |           2 |
| 1c            |           3 |
| 2c            |           3 |
| 3c            |           3 |
+---------------+-------------+

解决方案：

SELECT *
FROM (
SELECT DATE_EXAMPLE
,CONTAINERID
,ROW_NUMBER() OVER (
PARTITION BY CONTAINERID ORDER BY CONTAINERID
) ROW_NUM
FROM DATA
) A
WHERE ROW_NUM = 1

输出：

+---------------+--------------+---------+
| date_example  | containerID  | row_num |
+---------------+--------------+---------+
| 1a            |           1  |       1 |
| 1b            |           2  |       1 |
| 1c            |           3  |       1 |
+---------------+--------------+---------+