sql - Oracle SQL，如何选择组中的第一行？

Question

这是我的 SQL fiddle :http://sqlfiddle.com/#!4/75ab7/2

基本上，我创建了一个表并向其中插入了一些数据。

CREATE TABLE subject (
  id INT NOT NULL,
  seq_num INT NOT NULL,
  name VARCHAR(30) NOT NULL
);

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (1, 1, 'sub_1_1');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (2, 1, 'sub_1_2');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (3, 2,'sub_2_1');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (4, 2, 'sub_2_2');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (5, 2, 'sub_2_3');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (6, 3, 'sub_3_1');

INSERT INTO subject 
  (id, seq_num, name) 
VALUES 
  (7, 3, 'sub_3_1');

我运行这个选择语句:

select
  LISTAGG(TRIM(id), ',') WITHIN GROUP (ORDER BY 1) AS IDS,
  seq_num,
  LISTAGG(TRIM(name), ',') WITHIN GROUP (ORDER BY 1) AS NAMES
from
  subject
group by
  seq_num
order by
  seq_num asc

选择语句结果:

| ids   | seq_num | names                   |
|-------|---------|-------------------------|
| 1,2   | 1       | sub_1_1,sub_1_2         |
| 3,4,5 | 2       | sub_2_1,sub_2_2,sub_2_3 |
| 6,7   | 3       | sub_3_1,sub_3_1         |

我可以生成这样的东西吗？

| ids | seq_num | names   |
|-----|---------|---------|
| 1   | 1       | sub_1_1 |
| 3   | 2       | sub_2_1 |
| 6   | 3       | sub_3_1 |

那只是选择一组中的第一行。

Answer 1

使用行号:

select
    id, seq_num, name
from
(
    select id, seq_num, name,
        row_number() over (partition by seq_num order by id) rn
    from subject
) t
where rn = 1
order by seq_num;

这是更新后的 Fiddle 的链接:

Demo