Re: SQL: More fields in select list with group by

On Mon, 12 Oct 1998 20:03:24 +0200, Michael Hase <michael_at_six.de> wrote:

>Hi SQL experts,
>
>does someone know how to accomplish the following with Oracle:
>
>CREATE TABLE dummy (
> id int,
> version int,
> title varchar2(80)
>);
>
>INSERT INTO dummy VALUES ( 1, 1, 'Title 1, version 1' );
>INSERT INTO dummy VALUES ( 1, 2, 'Title 1, version 2' );
>INSERT INTO dummy VALUES ( 2, 1, 'Title 2, version 1' );
>INSERT INTO dummy VALUES ( 2, 2, 'Title 2, version 2' );
>INSERT INTO dummy VALUES ( 2, 3, 'Title 2, version 3' );
>INSERT INTO dummy VALUES ( 3, 1, 'Title 3, version 1' );
>
>I´m searching for a select statement that outputs
>
>id version title
>1 2 Title 1, version 2
>2 3 Title 2, version 3
>3 1 Title 3, version 1
>
>That is: for each id the one record with the max of the field version.
>Primary key is id+version, and I´d like to have the last version of the
>article, the others should remain in the table for archiving purposes.
>Splitting into more tables is no option here, since the data model is
>fixed.
>
>I fiddled around with some group by statement like:
>SELECT id, max(version) FROM dummy GROUP BY id;
>But I´d like to have the title in the output also.
>

You can use an inline view like...

SQL> l
  1 select d.id, d.version, d.title
  2 from dummy d,
  3 ( select id, max(version) version from dummy group by id ) m   4 where m.id = d.id
  5* and m.version = d.version
SQL> /  ID VERSION TITLE

--- -------- ------------------------------
  1        2 Title 1, version 2
  2        3 Title 2, version 3
  3        1 Title 3, version 1

SQL> hope this helps.

chris.

>Thanks in advance for any help,
>Michael
Received on Mon Oct 12 1998 - 16:19:02 CDT