CODEWITHSUNDEEP
sqlsql-server
martijn_himself
martijn_himself

Reputation: 1570

How to select row based on existance of value in other column

I realise the title to this question may be vague but I am not sure how to phrase it. I have the following table:

i_id  option  p_id
----  ------  ----
1     A       4
1     B       8
1     C       6
2     B       3
2     C       5
3     A       7
3     B       3
4     E       11

How do I select a row based on the value of the option column for each unique i_id: if 'C' exists, select the row, else select row with 'B' else with 'A' so that result set is: 

i_id  option  p_id
----  ------  ----
1     C       6
2     C       5
3     B       3

Upvotes: 2

Views: 215

Answers (4)

Luis Casillas
Luis Casillas

Reputation: 30237

Well, I would suggest that this problem can be made easier if you can assign a numeric "score" to each letter, such that "better" letters have higher scores. Then you can use MAX to find, for each group, the row with the highest "score" for the option. Since 'A' < 'B' < 'C', we could cheat here and use option as the score, and thus:

SELECT t1.i_id, t1.option, t1.p_id
  FROM thetable t1
  INNER JOIN (SELECT t2.i_id, MAX(option)
                FROM thetable t2
              GROUP BY t2.i_id) AS maximums
        ON t1.i_id = maximums.i_id
 WHERE option != 'D'

This assumes that {i_id, option} is a natural key of the table (i.e., that no two rows will have the same combination of values for those two columns; or, alternatively, that you have an uniqueness constraint on that pair of columns).

Upvotes: 1

GSerg
GSerg

Reputation: 78210

select i_id, option, p_id
from (
  select
    i_id,
    option,
    p_id,
    row_number() over (partition by i_id order by case option when 'C' then 0 when 'B' then 1 when 'A' then 2 end) takeme
  from thetable
  where option in ('A', 'B', 'C')
) foo
where takeme = 1

Upvotes: 2

ceth
ceth

Reputation: 45325

create table t2 (
  id int,
  options varchar(1),
  pid int
)

insert into t2 values(1, 'A', 4)
insert into t2 values(1, 'B', 8)
insert into t2 values(1, 'C', 6)
insert into t2 values(1, 'E', 7)

select t2.* from t2,
(select id, MAX(options) as op from t2
 where options <> 'E'
 group by id) t
where t2.id = t.id   and t2.options = t.op

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17570

This will give you the values ordered by C, B, A, while removing any i_id record that does not have one of these values.

WITH ranked AS
(
   SELECT i_id, [option], p_id
      , ROW_NUMBER() OVER (PARTITION BY i_id ORDER BY CASE [option]
                                                         WHEN 'C' THEN 1
                                                         WHEN 'B' THEN 2
                                                         WHEN 'A' THEN 3
                                                         ELSE 4
                                                      END) AS rowNumber
   FROM yourTable
   WHERE [option] IN ('A', 'B', 'C')
)
SELECT r.i_id, r.[option], r.p_id
FROM ranked AS r
WHERE r.rowNumber = 1

Upvotes: 2

Related Questions