Ollybear Ollybear - 18 days ago 9
SQL Question

Comparing SQL queries with current query values

How would I compare D1 to D2 and use the result as part of the query.

Select A.id,
(Select b.date from b,c where b.id=c.id and c.id = E.id and b = 1) as D1,
(Select b.date from b,c where b.id=c.id and c.id = E.id and b = 2) as D2
from
A,E


I want to get the lower of D1 and D2

Select A.id,
CASE
WHEN D1 >= D2
THEN D2
WHEN D2 > D1
THEN D1
END
FROM A,E

Answer

Check out the LEAST function:

SELECT id, LEAST(d1, d2) FROM {
    Select A.id,
    (Select b.date from b,c where b.id=c.id and c.id = E.id and b.id = 1) as d1,
    (Select b.date from b,c where b.id=c.id and c.id = E.id and b.id = 2)) as d2
    from 
    A,E 
)