Returning only the rows that have a count less than N in 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.

What do you do if we have a table that contains multiple version of a data row and we want to return only the rows that have a count less than N? The solution is to group by column id and then count number of rows per the grouped column. When we have the number of rows per each individual group, we can further reduce the count based on the having statement.

For this example, we will create a sample table that will hold multiple versions of user data:

 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 
11 INSERT INTO  table_name (id, version, desc_text) 
12 VALUES (1, 1, 'row 1, version 1'),
13        (1, 2, 'row 1, version 2'),
14        (1, 3, 'row 1, version 3'),
15        (1, 4, 'row 1, version 4'),
16        (2, 1, 'row 2, version 1'),
17        (2, 2, 'row 2, version 2'),
18        (3, 4, 'row 3, version 4'),
19        (3, 5, 'row 3, version 5'),
20        (3, 6, 'row 3, version 6');

We can now group by he column, id to get the row count per grouped items. Once we have the number or rows per each group, we can further reduce the count using the having statement.

 1 select  id, 
 2                 'row_count' = count(*)
 3 from    
 4                 table_name
 5 group by      
 6                 id
 7 having  
 8                count(*) < 3
 9 order by      
10                id ;