Re: Select all colums from a table wher X has its Maximum

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Tue, 19 Jul 2005 15:50:42 +0000 (UTC)
Message-ID: <dbj7gi$2ch$1@klatschtante.init7.net>

On 2005-07-19, Björn Wächter <Bjoern.Waechter_at_p3-solutions.de> wrote:
> Hi all,
>
> is there a way to select all columns from a table
> where for example comlum W has its maximum?
>
> Table A:
>
> W | X | Y |
> ------------
> 1 | 2 | 5 |
> 3 | 3 | 3 |
> 6 | 7 | 1 |
> 1 | 8 | 2 |
>
> What I do at the moment is:
>
> SELECT * FROM A
> WHERE W IN (SELECT MAX(W) FROM A)
>
> But is there a way with only one select statment?
>
>
>
> Or a little bit more complex:
>
> Table B:
>
> W | X | Y | I |
> ----------------
> 1 | 2 | 5 | 1 |
> 3 | 3 | 3 | 2 |
> 6 | 7 | 1 | 1 |
> 2 | 8 | 2 | 2 |
> 4 | 2 | 5 | 1 |
> 5 | 3 | 3 | 1 |
> 7 | 7 | 1 | 3 |
> 9 | 8 | 2 | 2 |
>
> SELECT * FROM B
> WHERE
> W IN (SELECT MAX(W) FROM B GROUP BY I)
>
>
> Where W is unique.

No, it's not possible without a nested
select statement (although Daniel is
right pointing out that this is still ONE statement).

However, it's more elegant, IMHO, to use analytic functions:

create table a (
  w number,
  x number,
  y number
);

insert into a values (1, 2, 5);
insert into a values (3, 3, 3);
insert into a values (6, 7, 1);
insert into a values (1, 8, 2);

select w,x,y
  from (
    select

      row_number() over (order by w desc) r,
      w,x,y

    from a
  )
where r = 1;

create table b (

  w number,
  x number,
  y number,

  i number
);

insert into b values (1, 2, 5, 1);
insert into b values (3, 3, 3, 2);
insert into b values (6, 7, 1, 1);
insert into b values (1, 8, 2, 2);
insert into b values (4, 2, 5, 1);
insert into b values (5, 3, 3, 2);
insert into b values (7, 7, 1, 1);
insert into b values (9, 8, 2, 2);

select w,x,y,i
  from (
    select

      row_number() over (
        partition by i
        order by w desc) r,
      w,x,y,i

    from b
  )
where r = 1;

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Tue Jul 19 2005 - 10:50:42 CDT