从重复的值中获取第一行的Oracle SQL查询。

huangapple go评论100阅读模式
英文:

how get first row from duplicata values Oracle SQL query

问题

以下是翻译好的部分:

下面是我的带有数值的表格(我的表格没有任何约束条件):

  1. SELECT DISTINCT * FROM khalil;

输出结果:

> > ID VALUE > -- ------- > 1 yassine > 1 khalil > 2 amine >

当我有重复数值时,我需要获取第一行。

我有两行具有 id = 1,所以在这种情况下,我需要获取第一行,它的 id = 1value = 'yassine'

英文:

Below you can find my table with values (there are no constraints on my table):

  1. 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的第一行。

英文:
  1. SELECT * FROM khalil
  2. WHERE ROWID IN (SELECT MIN(ROWID) FROM khalil GROUP BY id)
  3. ORDER BY id

This will return the first row for each id.

答案2

得分: 0

如果您不真的关心您将获得哪个 value(除非有些东西可以用来区分值),聚合函数 - 例如 minmax - 可以帮助:

  1. SQL> select id,
  2. 2 max(value) value
  3. 3 from khalil
  4. 4 group by id
  5. 5 order by id;
  6. ID VALUE
  7. ---------- --------------------
  8. 1 yassine
  9. 2 amine
  10. SQL>;

或者,使用分析函数(例如 row_number,它允许您 排序 值),您可以按如下方式执行:

  1. SQL> with temp as
  2. 2 (select id,
  3. 3 value,
  4. 4 row_number() over (partition by id order by value desc) rn
  5. 5 from khalil
  6. 6 )
  7. 7 select id,
  8. 8 value
  9. 9 from temp
  10. 10 where rn = 1
  11. 11 order by id;
  12. ID VALUE
  13. ---------- --------------------
  14. 1 yassine
  15. 2 amine
  16. 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:

  1. SQL> select id,
  2. 2 max(value) value
  3. 3 from khalil
  4. 4 group by id
  5. 5 order by id;
  6. ID VALUE
  7. ---------- --------------------
  8. 1 yassine
  9. 2 amine
  10. SQL>

Alternatively, using analytic functions (such as row_number, which lets you sort values), you'd do it as follows:

  1. SQL> with temp as
  2. 2 (select id,
  3. 3 value,
  4. 4 row_number() over (partition by id order by value desc) rn
  5. 5 from khalil
  6. 6 )
  7. 7 select id,
  8. 8 value
  9. 9 from temp
  10. 10 where rn = 1
  11. 11 order by id;
  12. ID VALUE
  13. ---------- --------------------
  14. 1 yassine
  15. 2 amine
  16. SQL>

huangapple
  • 本文由 发表于 2020年1月6日 22:32:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/59613880.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定