Re: Selecting minimum values of same column in three tables

Originally posted by Erwin
> Hi,
>
> I encountered the problem when I need to select the minimum
> values of the
> same column in the three tables
> The table will look like:
>
>
> Table 1
> ID #1 ID#2
> ------ -----
> a 3
> a 5
> a 7
> b 8
> b 9
> b 10
> c 15
> c 45
> c 12
>
> Table 2
> ID #1 ID#2
> ------ -----
> a 4
> a 8
> a 1
> b 2
> b 20
> b 30
> c 25
> c 75
> c 62
>
> and other tables
>
> and the following table I need to obtain:
>
> Table Result
> ID #1 ID#2
> ------ -----
> a 3
> a 5
> a 1
> b 2
> b 9
> b 10
> c 15
> c 45
> c 12
>
> Thanks for the comments in advance
It appears that you are relying on an additional column that you do not show like this:

    Table 1
ID #1 Seq ID#2
------ --- -----

a        1        3
a        2        5
a        3        7
b        1        8
b        2        9
b        3        10
c        1        15
c        2        45
c        3        12

Table 2
ID #1 Seq ID#2
------ --- -----

a        1        4
a        2        8
a        3        1
b        1        2
b        2        20
b        3        30
c        1        25
c        2        75
c        3        62

In which case the result is this:

Table Result
ID #1 Seq ID#2
------ --- -----

a        1        3
a        2        5
a        3        1
b        1        2
b        2        9
b        3        10
c        1        15
c        2        45
c        3        12

And the query is simply:

SELECT id#1, seq, min(id#2)
FROM
( SELECT id#1, seq, id#2 FROM table1
  UNION
  SELECT id#1, seq, id#2 FROM table2
);

If you believe that you can do this without the additional column (perhaps relying on some "physical order" of the rows), you are mistaken.

--
Posted via http://dbforums.com