CODEWITHSUNDEEP
sqloracle-database
sandip
sandip

Reputation: 434

Remove conditional duplicate value

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:

  1. Keep all rows where SR=0
  2. 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

Upvotes: 0

Views: 82

Answers (2)

David Faber
David Faber

Reputation: 12486

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.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 1

Related Questions