CODEWITHSUNDEEP
sqlaggregate-functions
mmtauqir
mmtauqir

Reputation: 9329

SELECT a different aggregate value if null?

So let's say I have the following data samples:

Sample 1:

id|name
-------
0 |0
0 |1
0 |2
0 |3
-------

Sample 2:

id|name
-------
0 |0
1 |1
3 |3
4 |4
-------

Sample 3:

id|name
-------
0 |0
1 |1
2 |2
3 |3
-------

What I want is to be able to return the smallest missing value in the sequence, if possible. If not, I want to return the MAX(id)+1 as the available value.

To get the smallest missing value, I do the following:

SELECT temptable.id+1 FROM (
    SELECT id, LEAD(id, 1) OVER (ORDER BY id) AS lead FROM mytable) AS temptable
WHERE (lead - id) > 1;

This will return NULL for Sample 1, 2 for Sample 2, and NULL for Sample 3.

Now is it possible to first check if temptable.id is NULL and if so, return the MAX(mytable.id) in a single query?

Upvotes: 2

Views: 94

Answers (4)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Using this sample data as per OP:

create table #t(
     sample int not null
    ,id     int not null
    ,name   int not null
)

insert #t(sample,id,name)
values (1,0,0),(1,0,1),(1,0,2),(1,0,3)
      ,(2,0,0),(2,1,1),(2,3,3),(2,4,4)
      ,(3,0,0),(3,1,1),(3,2,2),(3,3,3);

this SQL ill perform as required for each sample:

declare @sample int = 1;

SELECT top 1 temptable.id+1 FROM (
    SELECT id, LEAD(id, 1) OVER (ORDER BY id) AS lead 
    FROM (
        select id
        from #t
        where sample = @sample
        union all
        select 2147483647  -- = 2^31-1 = MAXINT for integer datatype
    ) t
) AS temptable
WHERE (lead - id) > 1

yielding on each run:

Sample      id
----------- -----------
1           1

Sample      id
----------- -----------
2           2

Sample      id
----------- -----------
3           4

Upvotes: 0

Jaugar Chang
Jaugar Chang

Reputation: 3196

I think the easiest way is to add a very big id after all your records. Then your query will never return null value at all.

First way just insert a row:

Insert into mytable values(2147483648,0);

Second way use union all:

SELECT temptable.id+1 FROM (
SELECT id, LEAD(id, 1) OVER (ORDER BY id) AS lead FROM 
  (select * from mytable union all select 2147483648,0) ) AS temptable
WHERE (lead - id) > 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Yes. Do the calculation for the maximum in the subquery and then use coalesce():

SELECT coalesce(id+1, maxid + 1)
FROM (SELECT id, LEAD(id, 1) OVER (ORDER BY id) AS lead,
             MAX(id) OVER () as maxid
      FROM mytable
     ) t
WHERE (lead - id) > 1;

By the way, you can do this with not exists:

select min(id) + 1
from mytable t1
where not exists (select 1 from mytable t2 where t2.id = t1.id + 1);

I think this does both calculations at the same time. If the ids are all in sequence, then only the largest id passes the where condition.

Upvotes: 1

jcaron
jcaron

Reputation: 17720

I guess what you are looking for is COALESCE:

COALESCE(value1,value2,value3...)

will return the first non-null value.

Another option is to use CASE WHEN ... THEN ... ELSE ... END, for instance:

CASE WHEN value IS NOT NULL THEN value ELSE other_value END

Upvotes: 0

Related Questions