CODEWITHSUNDEEP
sqloracle-database
Ollybear
Ollybear

Reputation: 13

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

Upvotes: 0

Views: 53

Answers (3)

C. Smith
C. Smith

Reputation: 411

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 
)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use sub-select

select id,
       CASE WHEN D1 >= D2 THEN D2 ELSE D1 END
From 
(
/* Your Query */
) A

Start using JOIN syntax instead of old style comma separated join. Here is the correct way to do this

SELECT A.id,
       D1,
       D2,
       Least(D1, D2)
FROM   A CROSS JOIN E
       LEFT JOIN (SELECT Max(CASE WHEN b = 1 THEN date END) AS D1,
                         Max(CASE WHEN b = 2 THEN date END) AS D2,
                         E.Id
                  FROM   b
                         INNER JOIN c
                                 ON b.id = c.id
                  WHERE  b IN ( 1, 2 )
                  Group by E.Id) D
              ON c.id = D.id

Upvotes: 0

Hogan
Hogan

Reputation: 70523

I think you probably want something like this:

Select A.id, min(b.date)
from A
cross join b
join c on b.id = c.id
join E on c.id = E.id
where b in (1,2) 
group by A.id

Upvotes: 1

Related Questions