CODEWITHSUNDEEP
sqloracle-databasegroup-bydistinct
Kushal Karia
Kushal Karia

Reputation: 97

How to select unique records from a result in oracle SQL?

I am running a SQL query on oracle database.

SELECT   DISTINCT flow_id , COMPOSITE_NAME  FROM CUBE_INSTANCE where flow_id IN(200148,
200162);

I am getting below results as follow.

200162  ABCWS1
200148  ABCWS3
200162  ABCWS2
200148  OutputLog
200162  OutputLog

In this result 200162 came thrice as composite Name is different in each result. But my requirement is to get only one row of 200162 which is 1st one. If result contains same flow_id multiple times then it should only display result of first flow_id and ignore whatever it has in 2nd and 3rd.

EXPECTED OUTPUT - 

200162  ABCWS1
200148  ABCWS3

Could you please help me with modification of query?

Thank you in advance !!!

Upvotes: 0

Views: 170

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

There is no such thing as a "first" row, unless a column specifies that information.

But you can easily use aggregation for this purpose:

select ci.flow_id, min(ci.composite_name)
from cube_instance ci
where flow_id in (200148, 200162);
group by ci.flow_id

If you do have a column that specifies the ordering, you can still use aggregation. The equivalent of the "first" function in Oracle is:

select ci.flow_id,
       min(ci.composite_name) keep (dense_rank first order by <ordering col>)
from cube_instance ci
where flow_id in (200148, 200162);
group by ci.flow_id

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

It appears that you want to take the lexicographically first composite name for each flow_id:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY flow_id ORDER BY COMPOSITE_NAME) rn
    FROM CUBE_INSTANCE t
    WHERE flow_id IN (200148, 200162)
)

SELECT flow_id, COMPOSITE_NAME
FROM cte
WHERE rn = 1;

Upvotes: 1

Related Questions