Return the last version of data row from a table
Build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in—from in-memory performance and advanced security to in-database analytics.
There are many ways to return the last version of the data row from a table that contains multiple versions of data rows. This particular solution is based on the usage of a primary key and uses a sub-query against it. To illustrate this, let's create a sample table that will hold multiple version of user data and populate the table with sample data as such:
1 create 2 table table_name ( 3 id int, 4 version int, 5 desc_text varchar(100), 6 primary key (id, version) 7 ); 8 9 10 INSERT INTO table_name (id, version, desc_text) 11 VALUES (1, 1, 'row 1, version 1'), 12 (1, 2, 'row 1, version 2'), 13 (1, 3, 'row 1, version 3'), 14 (1, 4, 'row 1, version 4'), 15 (2, 1, 'row 2, version 1'), 16 (2, 2, 'row 2, version 2'), 17 (3, 4, 'row 3, version 4'), 18 (3, 5, 'row 3, version 5'), 19 (3, 6, 'row 3, version 6');
Now we will create a query that uses a primary key then uses a subquery against it
1 select 2 A.id , 3 A.version, 4 A.desc_text 5 from 6 table_name A 7 where 8 A.version = 9 ( 10 select max(Z.version) 11 from table_name Z 12 where Z.id = A.id 13 ) 14 order by 15 id;