Re: Selecting minimum values of same column in three tables

Hi Andrew,

It is indeed that I did not include the other column: seq 2, but how did you find out? Just wondering.

This is what I would like to achieve but in each of the select statement group, it contains group by and order by expression and when I tried to enclose the first select statement with brackets around it, it gave me error of not recognizing the identified in the group by statement. Anything wrong with the bracketing ?

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

Thanks !!

"andrewst" <member14183_at_dbforums.com> wrote in message news:2914778.1053683909_at_dbforums.com...
>
> 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
Received on Fri May 23 2003 - 16:47:11 CEST