Returning only the rows that have a count less than N in a table that contains multiple versions of data rows
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 ;