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