CODEWITHSUNDEEP
sqlitemaxmin
kosbou
kosbou

Reputation: 3999

sqlite select minimum and maximum from two tables

I have two tables

table1

c1t1    c2t1
1       saanu
3       abc

table2

c1t2    c2t2
2       val2
4       val4

I have to find out the values of c2t1 and c2t2 for the minimum and maximum value of c1t1 and c1t2 with one line command.

For the above example I have to find saanu and val4

Upvotes: 0

Views: 625

Answers (3)

Marcelo Bielsa
Marcelo Bielsa

Reputation: 142

I had a very similar problem and solved it with UNION ALL. The minimum of the aColumn column in tables aTable1, ... , aTableN can be computed as:

SELECT Min(aColumn) 
FROM (
SELECT aColumn FROM aTable1 UNION ALL
SELECT aColumn FROM aTable2 UNION ALL
...
SELECT aColumn FROM aTableN) t;

You should be able to do Min in each of the inner selects, but I haven't found out how to do that!

Upvotes: 2

user359040
user359040

Reputation:

One approach:

select max(case c1 when min1 then c2 end) c2_first, 
       max(case c1 when max1 then c2 end) c2_last 
from (select c1t1 c1, c2t1 c2 from table1
       union all
       select c1t2 c1, c2t2 c2 from table2) u
cross join
(select min(min11, min12) min1, max(max11, max12) max1 from
 (select min(c1t1) min11, max(c1t1) max11 from table1) t1
 cross join
 (select min(c1t2) min12, max(c1t2) max12 from table2) t2) m

SQLFiddle here.

Upvotes: 1

Alberto
Alberto

Reputation: 15941

1)

SELECT c2t1
FROM table1
ORDER BY c1t1 ASC LIMIT 1

2)

SELECT c2t2
FROM talbe2
ORDER BY c1t2 DESC LIMIT 1

Upvotes: 0

Related Questions