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

Michael Hase 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.
>
> Thanks in advance for any help,
> Michael
> --
> Michael Hase michael_at_six.de
> Six Offene Systeme GmbH
> http://www.six.de Sielminger Str. 63
> phone +49 711 99091 62 70771 Leinfelden-Echterdingen, Germany

  Hello Michael

Try the next :

SELECT a.id, a.version, a.title FROM dummy a, (SELECT id, max(version) version2 FROM dummy GROUP BY id) b
where a.id = b.id AND a.version = b.version2

Leonel Sanhueza J.
Universidad de Concepcion
lsanhuez_at_udec.cl Received on Mon Oct 12 1998 - 19:49:24 CDT