Re: self-join

From: Andy Triggs <andrew.triggs_at_businessobjects.com>
Date: 9 Jul 2002 08:30:51 -0700
Message-ID: <2b6e86d0.0207090730.7e41ec72_at_posting.google.com>

Its possible witha nested query. For example the following should work:

select table1.*
from table1,

     (select table1.id, max(table1.num) maxval
      from table1
      group by table1.id) nest

where table1.id = nest.id
and nest.maxval = 3;

Regards, Andy

"Sanjin" <sanjin.m_at_SoftHome.net> wrote in message news:<agd78s$4v3h$1_at_as201.hinet.hr>...
> I.e., I have the data that is returned from the following statement 'SELECT
> table1.id, MAX(table1.num) FROM table1 GROUP BY table1.id'
>
> i.e.
> table1.id | (MAX(table1.num)
> 1 3
> 2 3
> 3 5
> 4 1
>
> I want to use returned data like a filter for the retrieving the other data
> (columns) from the same table ('table1') (data that matches criteria
> returned from the first statement).
>
> Is it possible to achieve that in one sql statement? I have thought to use
> self-join.
>
> Thanks in advance
>
> Sanjin
Received on Tue Jul 09 2002 - 17:30:51 CEST