CODEWITHSUNDEEP
sqlselectgroup-bydb2
bluelurker
bluelurker

Reputation: 1444

Select rows based on multiple conditions

I have a table from which I have to select some rows based on the following conditions.

  1. If more than one row exists with same DocumentRef, then select all the rows if BlockNumber is empty for all rows
  2. If more than one row exists with same DocumentRef, then select only 1 row (ordered by DocumentId asc) with BlockNumber IS NOT EMPTY
  3. If only one row exists with DocumentRef, select it irrespective of anything

Table: enter image description here

I was trying to group it by DocumentRef and filter with having but having can only have aggregate functions. I think I will have to provide multiple conditions in having separated by OR. Please give me some direction.

Upvotes: 2

Views: 1502

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use window functions:

select t.*
from (select t.*,
             sum(case when blocknumber is not null then 1 else 0 end) over (partition by documentref) as num_bn_notnull,
             rank() over (partition by documentref
                          order by (case when blocknumber is not null then documentid end) desc nulls last
                         ) as rnk
      from t
     ) t
where num_bn_notnull = 0 or
      rnk = 1;

Or, you can use exists clauses:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.documentref = t.documentref and
                        t2.blocknumber is not null
                 ) or
       t.documentid = (select max(t2.documentid)
                       from t t2
                       where t2.documentref = t.documentref and
                             t2.blocknumber is not null
                      );

This can take advantage of an index on (documentref, blocknumber, documentid).

Actually, by a quirk of the SQL language, I think this works as well:

select t.*
from t
where t.documentid >= any (select t2.documentid
                           from t t2
                           where t2.documentref = t.documentref and
                                 t2.blocknumber is not null
                           order by t2.documentid
                           fetch first 1 row only
                          );

The subquery returns an empty set if all blocknumbers are NULL. By definition, any document id matches the condition on an empty set.

Upvotes: 1

forpas
forpas

Reputation: 164089

Join the table to a query that returns for each documentref the maximum documentid for all the blocknumbers that are not null or null if they are all null:

select t.*
from tablename t inner join (
  select 
    documentref,
    max(case when blocknumber is not null then documentid end) maxid 
  from tablename
  group by documentref
) d on d.documentref = t.documentref 
   and t.documentid = coalesce(d.maxid, t.documentid)

See the demo.
Results:

> DOCUMENTID | DOCUMENTREF | WARDID | BLOCKNUMBER
> ---------: | ----------: | -----: | ----------:
>  203962537 |   100000126 |      B |           A
>  203962538 |   100000130 |      B |           A
>  203962542 |   100000151 |   null |        null
>  203962543 |   100000151 |   null |        null
>  203962544 |   100000180 |      B |           A
>  203962546 |   100000181 |      B |           A
>  203962551 |   100000185 |   null |        null
>  203962552 |   100000186 |      B |           A

Upvotes: 1

Related Questions