Returning the first version of the data row from a table that contains multiple versions of data rows

walden systems, geeks corner, programming, languages, developer, sql, datetime, varchar, time, date, rows, table, pivot, top, join
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 first 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 INSERT INTO  table_name (id, version, desc_text) 
10 VALUES (1, 1, 'row 1, version 1'),
11        (1, 2, 'row 1, version 2'),
12        (1, 3, 'row 1, version 3'),
13        (1, 4, 'row 1, version 4'),
14        (2, 1, 'row 2, version 1'),
15        (2, 2, 'row 2, version 2'),
16        (3, 4, 'row 3, version 4'),
17        (3, 5, 'row 3, version 5'),
18        (3, 6, 'row 3, version 6');

Now we will create a query that uses a primary key then uses a sub query 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 min(Z.version) 
11           from   table_name  Z 
12           where  Z.id = A.id
13        )
14 order by     
15        id;