如何根据另一行中的优先级值选择一行? [英] How do I select a row based on a priority value in another row?

问题描述

我正在使用Oracle 11G，并且我有一个包含以下列和值的表，并且我想根据优先级列为每个列选择值.每个ID只需要一行.

I am using Oracle 11G and I have a table with the following columns and values and I want to select the value for each column based on the priority column. I only want one row for each ID.

ID    NAME   NAME_PRIORITY   COLOR   COLOR_PRIORITY
1     SAM       2             RED          1
1     SAM       2             GREEN        2
1     JOHN      1             BLUE         3
2     MARY      2             ORANGE       1
3     JON       2             RED          2
3     PETE      3             GREEN        1

所需结果

ID   NAME    NAME_PRIORITY   COLOR     COLOR_PRIORITY
1    JOHN       1             RED           1
2    MARY       2             ORANGE        1
3    JON        2             GREEN         1

如何选择优先级最低的名称和颜色，每个ID仅包含一行.

How do I select the NAME and COLOR with the lowest PRIORITY # and only have one row for each ID.

推荐答案

一个选项是:

select d.id, min(name) keep (dense_rank first order by name_priority) name,
       min(name_priority) name_priority,
       min(color) keep (dense_rank first order by color_priority) color,
       min(color_priority) color_priority
  from yourtab d
 group by id;

这篇关于如何根据另一行中的优先级值选择一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！