Let's say I have a result like the following that comes from a complex set of queries from multiple tables (it's not a table) using Oracle Server in background.
This comes when I have a where condition like type in ('A','B') . I have other types too in the source tables but right now I'm working with only these two types.
| ID | code | type |
|---|---|---|
| 1 | 123 | A |
| 2 | 214 | B |
| 3 | 336 | A |
| 1 | 245 | B |
| 2 | 214 | A |
| 4 | 123 | B |
Now I want to have only 1 record for a single ID. That is, type A if it exist else type B. If both exists for the same ID then only type A.
There are additional columns to this result where the data remains same for a single ID except the code and type
The result should be something like the following
| ID | code | type |
|---|---|---|
| 1 | 123 | A |
| 3 | 336 | A |
| 2 | 214 | A |
| 4 | 123 | B |
I tried using group function with an outer query but cant seem apply any group function over the whole result.
I tried using union with each type on its own. But it again gives both types.
I'm not sure how can I get my desired results.
You can use ROW_NUMBER()-functionality to achieve this result, e.g.
WITH YOUR_TABLE_DATA (ID,CODE,TYPE)
AS
(
SELECT 1, 123,'A' FROM DUAL UNION ALL
SELECT 2 , 214, 'B' FROM DUAL UNION ALL
SELECT 3 , 336 , 'A' FROM DUAL UNION ALL
SELECT 1 , 245 ,'B' FROM DUAL UNION ALL
SELECT 2 , 214 , 'A' FROM DUAL UNION ALL
SELECT 4, 123 , 'B' FROM DUAL
)
SELECT X.ID,X.CODE,X.TYPE FROM
(
SELECT Y.ID,Y.CODE,Y.TYPE,
ROW_NUMBER()OVER(PARTITION BY Y.ID ORDER BY Y.TYPE ASC)XCOL
FROM YOUR_TABLE_DATA Y
)X WHERE X.XCOL=1