select two records from same table under different condition?

[xq]

i have to select review_dt from table events where flow_no is minimum, and i also have to select effect_dt from same table where flow_no is maximum, is there any other way of doing it instead of using UNION? thanks a lot!

 

[grega]

What about ...

select review_dt from events
where flow_no = (select min(flow_no) from events)
or flow_no = (select max(flow_no) from events)

Greg.

 

[xq]

thanks, but actually review_dt is only under the condition of minimum flow_no, and i have to select effect_dt as well where flow_no is maximum .

 

[grega]

Sorry ... should have read the question properly ;-)

I don't think it can be done in a single select. Why do you need a single query?

Greg.

 

[xq]

i have to use a single query, actually this query will involve other things as well, it is a very big query. thanks anyway!

 

[angiole]

You might try the following, whichs works well in one statement:

Assuming a length of 10 for each of flow_no,effec_dt, rev_dt:

SELECT SUBSTR(MAX(FLOW_NO||EFF_DT),11,10),
SUBSTR(MIN(FLOW_NO||REV_DT),11,10)
FROM TABLE1
WHERE ...

AA :~)

 

[PruSQLer]

I got a syntax error on your code, Angiole. The following seems to work:

Select (select rev_dt
from table1
Where flow_no = (Select min(flow_no)
From table 1) ) ,
(select eff_dt
from table1
Where flow_no = (Select max(flow_no)
From table 1) )
From Table1
where flow_no = (Select min(flow_no)
From table 1)
Or flow_no = (Select max(flow_no)
From table 1)

I don't know how this fits in with the rest of your query of course.

 

[angiole]

To PruSQLer, I should have mentioned that all column would ahve to converted to alpha format, to maintain sizes.

AA :~(

 

[xq]

thanks a lot!