# Task 12 (painting)

–Under assumption that there are holes among IDs (q_id) of squares, find the minimal and maximal "free" IDs in a range

–between available maximal and minimal IDs.

–If holes are not present, deduce NULL.

–Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6

select q_min, q_max from

(

select MIN(ID) q_min from

(

select Q_ID + 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ )

except

select

Q_ID from utQ) z) z

,

(select MAX(ID) q_max from

(

select Q_ID 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ ) AND Q_ID > (SELECT MIN(Q_ID) from utQ )

except

select

Q_ID from utQ) y) y

Forum’s solution:

select Min(x), Max(x) from
(select q_id-1 from utQ where q_id > (select Min(q_id) from utQ)
union all
select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)
except
select q_id   from utQ) Q(x)

cost 0.02839327044785
operations 19
то же, но через left join:

``````select Min(x), Max(x) from
(select q_id-1 from utQ where q_id > (select Min(q_id) from utQ)
union all
select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
left join utQ on x=q_id where q_id is null

cost	0.024536108598113
operations	17``````

то же, но через not exists:

``````select Min(x), Max(x) from
(select q_id-1 from utQ where q_id > (select Min(q_id) from utQ)
union all
select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.024512492120266
operations	16``````

Но еще лучше, конечно же вот так:

``````select Min(x), Max(x) from
(select q_id+a from utQ, (select -1 union all select 1) A(a)
where q_id > (select Min(q_id) from utQ)
and q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.015696810558438
operations	14``````