Group by key and return only one of the resulting rows

Question

I have the following data in a simple table:

ID | TYPE
---------
1  | 1
1  | 2
2  | 1
3  | 2
4  | 1
4  | 2
4  | 3
5  | 1
5  | 3
6  | 3

I now need to query this data in the following way:

• For each ID, only one row should be returned
  • If there is only one row for the ID return it.
  • If there are multiple rows for the ID, return the one with TYPE = 2. If that doesn't exist, return the one with TYPE = 3.
• TYPE can be either 1, 2 or 3
• ID and TYPE have a composite unique index, meaning that per ID there can be a maximum of three rows.

Question: How would I put this logic in a query?

---

Expected result:

ID | TYPE
---------
1  | 2
2  | 1
3  | 2
4  | 2
5  | 3
6  | 3

Answer 1

Another approach, minimizes the CASE WHEN branching: http://www.sqlfiddle.com/#!4/9957d/17

select id, 

    coalesce( sum(case when type=2 then 2 end), max(type) ) as type

from tbl
group by id
order by id

Output:

| ID | TYPE |
-------------
|  1 |    2 |
|  2 |    1 |
|  3 |    2 |
|  4 |    2 |
|  5 |    3 |
|  6 |    3 |

Answer 2

Based on the suggestion by podiluska and Dems, I am now using this:

with type_order as
(
    select 1 as type, 0 as prio from dual
    union all
    select 2 as type, 2 as prio from dual
    union all
    select 3 as type, 1 as prio from dual
)
select id, type from 
( 
   select yt.*, ROW_NUMBER() over (partition by id order by o.prio desc) as rn 
   from yourtable yt
   inner join type_order o on yt.type = o.type
)v 
where rn=1 

This is very intention revealing and simple to extend. If there will be more types in the future, this type_order inline view can be refactored into a real table.

Answer 3

Oracle has analytic functions in all recent versions.

WITH
  sequenced AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id
                           ORDER BY CASE type WHEN 2 THEN 1
                                              WHEN 3 THEN 2
                                              WHEN 1 THEN 3 END) AS sequenced_id
  FROM
    yourTable
)
SELECT
  *
FROM
  sequenced
WHERE
  sequence_id = 1

With this technique you will return all the values in the table, rather than just the PrimaryKey and the search value.

EDIT:

The best way of dealing with that is to have another table that has the order of preference for each type.

 type | preference_value
------+------------------
  2   |         1
  3   |         2
  1   |         3

This states that 2 is the most preferential, 3 is the second preference, and 1 is the third preference.

Because it's such a small group, however, it can also be done just with a simple CASE statement. See my amended query above.

Answer 4

Having fun with MOD() function:

SELECT 
    id,
    MOD(MIN(MOD(type+1,3))+1,3)+1 AS type
FROM
    tableX
GROUP BY
    id ;

Answer 5

I'd use a little hack.

SELECT ID, IF(SUM(TYPE) IN (5, 6), 2, MAX(TYPE)) AS TYPE
FROM table
GROUP BY ID

Answer 6

Here's my take: http://www.sqlfiddle.com/#!4/9957d/4

select id, 
    case when sum(case when type = 2 then 1 end) = 1 then
       2
    else
       max(type)
    end   
from tbl
group by id
order by id

Output:

| ID | TYPE |
-------------
|  1 |    2 |
|  2 |    1 |
|  3 |    2 |
|  4 |    2 |
|  5 |    3 |
|  6 |    3 |

Answer 7

Try this

select id, type from
(
   select *, ROW_NUMBER() over (partition by id order by abs(2.25-type) ) as rn
   from yourtable
)v
where rn=1

Answer 8

Since you know there are no TYPE values higher than 2, you can just do this:

select ID, max(TYPE) as TYPE
from MyTable
group by ID