CODEWITHSUNDEEP
sqloracle-database
Shashank Shrivastava
Shashank Shrivastava

Reputation: 1

How to get the desired results for this scenario [SQL]

I need to build a query for the following scenario :

Input table:

col1    col2    col3    col4
-------------------------------
1       2       a       pi
1       4       a       ci
1       2       a       ci
2       3       a       pi
2       4       a       ci
1       3       a       ci
1       3       a       pi

Logic required:

Fetch all the records from the input table except for the records matching below condition.

If value of group (a,b) is same for multiple rows, then only keep the row that has d='pi' Ex: for row 1 & 3 value of (a,b) = (1,2) we need to keep only row 1 that has d='pi'.

Final desired output: 

col1    col2    col3    col4 
---------------------------------
1       2       a       pi
1       4       a       ci
2       3       a       pi
2       4       a       ci
1       3       a       pi

Please help me out.

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can approach this with analytic functions, if you like:

select a, b, c, d
from (select t.*,
             row_number() over (partition by a, b
                                order by (case when d = 'pi' then 1 else 2 end)
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 1

wvdz
wvdz

Reputation: 16651

use a where not exist construct:

SELECT * FROM tab1 t
WHERE NOT EXISTS (SELECT 1 FROM tab1
                  WHERE t.a = a AND t.b = b)
OR d ='pi'

Upvotes: 0

Related Questions