英文:
how get first row from duplicata values Oracle SQL query
问题
以下是翻译好的部分：
下面是我的带有数值的表格（我的表格没有任何约束条件）：
SELECT DISTINCT * FROM khalil;
输出结果：
> > ID VALUE > -- ------- > 1 yassine > 1 khalil > 2 amine >
当我有重复数值时，我需要获取第一行。
我有两行具有 id = 1，所以在这种情况下，我需要获取第一行，它的 id = 1 和 value = 'yassine'。
英文:
Below you can find my table with values (there are no constraints on my table):
SELECT DISTINCT * FROM khalil;
outputs:
>
> ID VALUE
> -- -------
> 1 yassine
> 1 khalil
> 2 amine
>
I need to get the first row when I have duplicate values.
I have two rows with id = 1 so, in this case, I need that the first one,
which is id = 1 and value = 'yassine'
答案1
得分: 1
这将返回每个id的第一行。
英文:
SELECT * FROM khalil
WHERE ROWID IN (SELECT MIN(ROWID) FROM khalil GROUP BY id)
ORDER BY id
This will return the first row for each id.
答案2
得分: 0
如果您不真的关心您将获得哪个 value（除非有些东西可以用来区分值），聚合函数 - 例如 min 或 max - 可以帮助：
SQL> select id,
2 max(value) value
3 from khalil
4 group by id
5 order by id;
ID VALUE
---------- --------------------
1 yassine
2 amine
SQL>;
或者，使用分析函数（例如 row_number，它允许您 排序 值），您可以按如下方式执行：
SQL> with temp as
2 (select id,
3 value,
4 row_number() over (partition by id order by value desc) rn
5 from khalil
6 )
7 select id,
8 value
9 from temp
10 where rn = 1
11 order by id;
ID VALUE
---------- --------------------
1 yassine
2 amine
SQL>;
英文:
If you don't really care which value you'll get (unless there's something you can use to distinguish values), aggregates - such as min or max - can help:
SQL> select id,
2 max(value) value
3 from khalil
4 group by id
5 order by id;
ID VALUE
---------- --------------------
1 yassine
2 amine
SQL>
Alternatively, using analytic functions (such as row_number, which lets you sort values), you'd do it as follows:
SQL> with temp as
2 (select id,
3 value,
4 row_number() over (partition by id order by value desc) rn
5 from khalil
6 )
7 select id,
8 value
9 from temp
10 where rn = 1
11 order by id;
ID VALUE
---------- --------------------
1 yassine
2 amine
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论