Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Stuck trying to get max version number from table

user16854May 5 2010 — edited May 5 2010
We have a custom table which holds details about installed work packets.

Each time a packet is updated, a new entry is added to the table, with an incremented version number.

Here is some sample data:
GET sampledata
WITH sampledata AS
     (SELECT 'TEST0003' NAME
           , '1.1' VERSION
           , 'Installed Work Packet TEST 111' description
           , '18-Jul-2003' install_date
        FROM DUAL
      UNION ALL
      SELECT 'TEST0003'
           , '1.2'
           , 'Installed Work Packet TEST 111'
           , '18-Aug-2003'
        FROM DUAL
      UNION ALL
      SELECT 'TEST0003'
           , '1.3'
           , 'Installed Work Packet TEST 111'
           , '18-Sep-2003'
        FROM DUAL
      UNION ALL
      SELECT 'THIS2003'
           , '2.1'
           , 'Something Else'
           , '01-Jul-2009'
        FROM DUAL
      UNION ALL
      SELECT 'THIS2003'
           , '2.2'
           , 'Something Else'
           , '10-Aug-2009'
        FROM DUAL
      UNION ALL
      SELECT 'THIS2003'
           , '2.3'
           , 'Something Else'
           , '15-Nov-2009'
        FROM DUAL)
SELECT *
  FROM sampledata;
I would like to find out how to only return the highest version of each work packet from the table, but I can't work it out.

For example, from the sample data above, I'd like to only include:
NAME               VERSION                DESCRIPTION                            INSTALL_DATE
---------------------------------------------------------------------------------------------------
TEST0003           1.3                    Installed Work Packet TEST 111         18-Sep-2003
THIS2003           2.3                    Something Else                         15-Nov-2009
I can see that it'd need to somehow to select the MAX(version) for each different 'NAME', but cannot get my head around the syntax. Would I need to GROUP BY 'NAME', and then select the MAX(VERSION) from that?

Any advice much appreciated.

Thanks
This post has been answered by 189821 on May 5 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 2 2010
Added on May 5 2010
5 comments
855 views