Re: SQL question

Hi, Curt!

>SELECT MIN(number)
>FROM table
>WHERE description1 = 'TOR' or description2 = 'TOR';
>is there a clean way to know which side of the 'or' statement was
satisfied?

Maybe this way:
SELECT LEAST(NVL(t1.number1, t2.number2+1), NVL(t2.number2, t1.number1)) AS min_number,

             DECODE(LEAST(NVL(t1.number1, t2.number2+1), NVL(t2.number2, t1.number1)), t1.number1, 'DESCR1', t2.number2, 'DESCR2', 'NONE') AS source FROM
(SELECT MIN(number) AS number1
FROM table
WHERE description1 = 'TOR') t1,
(SELECT MIN(number) AS number2
FROM table
WHERE description2 = 'TOR') t2

Thus produced only two fullscans (or index-range scan if there are indexes on description1 and 2 columns). I think that Michel's way will do an additional fullscan (or index range (unique) scan if there's an index on NUMBER column) and sorting, but is much simplier to write and understand.

With best wishes,
Dmitry Sirotkin. Received on Thu Feb 10 2000 - 05:46:53 CST