Remove conditional duplicate value

Question

I am using 16 tables in query and try to optimized query with applying the below new changes. So we cannot use the group by, or union clause as it not be possible to apply in existing query due to performance issue.

I am looking for partition by rank/count/rownumber means the row level solution if possible.

select * FROM (
SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'AC' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'HA' as CODE,1 AS SR FROM DUAL UNION ALL
SELECT 'OF' as CODE,1 AS SR FROM DUAL )
ORDER BY SR

With the example data above query I want:

Keep all rows where SR=0
Remove SR=1 row where CODE value exists in (SR=0)

For example the "AC" row with SR=1 is included because there is not an "AC" row with SR=0; and the "HA" row with SR=1 is removed because there is an "HA" row with SR=0.

Required output for this data:

HA   0
OF   0
HA   0
OF   0
XY   1
XY   1
AC   1

Alex Poole · Accepted Answer · 2014-11-06 13:16:46Z

1

You could add a subquery and an analytic min():

select code, sr
from (
  select code, sr, min(sr) over (partition by code) as min_sr
  from (
    <your query>
  )
)
where sr = 0
or (sr = 1 and min_sr = 1)
order by sr;

For each row the subquery gets an additional column with the lowest SR for that code across all rows. The outer query then filters those so that rows with SR=1 are only kept if the minumum for that code is also 1; i.e. that there is no row for that code with SR=0.

With your dummy data that gives:

CODE         SR
---- ----------
OF            0 
OF            0 
HA            0 
HA            0 
XY            1 
XY            1 
AC            1

SQL Fiddle demo using a CTE with your data, and showing the extra analytic-min column in the subquery as well.

You could also simplify that to just where sr = min_sr, but separating it into two clauses might make the logic and rules clearer.

edited Nov 6, 2014 at 13:16

answered Nov 6, 2014 at 12:56

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Faber · Accepted Answer · 2014-11-06 15:11:49Z

One might also try the following, putting the subquery above into a WITH:

WITH s AS (
    SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'HA' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'AC' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,0 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'XY' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'HA' as CODE,1 AS SR FROM DUAL UNION ALL
    SELECT 'OF' as CODE,1 AS SR FROM DUAL )
SELECT code, sr FROM s s1 -- 's1' is the table alias
 WHERE s1.sr = 0
    OR ( s1.sr = 1
     AND NOT EXISTS ( SELECT 1 FROM s s2 -- 's2' is the table alias
                       WHERE s2.code = s1.code
                         AND s2.sr = 0 ) )

Result:

HA  0
HA  0
OF  0
OF  0
AC  1
XY  1
XY  1

I'm divided on whether I think this is more readable than @Alex Poole's answer, though. But it does avoid the analytic function.

Collectives™ on Stack Overflow

Remove conditional duplicate value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related