选择仅限id最小的行

suppose I have the following table X:

假设我有下表X：

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel
5   4   Darren
6   4   Jay

What will be the select statement to generate:

要生成的select语句是什么：

Id  Type  Name
1   1   Jane
2   2   Mary
3   3   Rose
4   4   Rachel

that only the first row for each type will be selected? Appreciate a lot.

只会选择每种类型的第一行？欣赏很多。

3 个解决方案

#1

A simple method is to use a correlated subquery:

一种简单的方法是使用相关子查询：

select t.*
from t
where t.id = (select min(t2.id) from t t2 where t2.type = t.type);

Here is the DEMO for the same.

这是同样的DEMO。

#2

Please try

请尝试

 SELECT t.* FROM test t
 INNER JOIN (SELECT MIN(id) id FROM test GROUP BY TYPE) t2 ON  t.id = t2.id;

#3

You may use row_number() analytic function if your database supports it.

如果数据库支持，您可以使用row_number（）分析函数。

SELECT Id
    ,Type
    ,Name
FROM (
    SELECT X.*
        ,row_number() OVER (
            PARTITION BY type ORDER BY Id
            ) rn
    FROM X
    ) a
WHERE rn = 1;

Demo

演示

#1