SQL to get unique rows in Netezza DB

Question

I have a table with rows like:

id    group_name_code  
1     999  
2     16  
3     789  
4     999  
5     231  
6     999  
7     349  
8     16  
9     819  
10     999  
11     654  

But I want output rows like this:

id    group_name_code
1     999  
2     16  
3     789  
4     231  
5     349  
6     819  
7     654  

Will this query help?

select id, distinct(group_name_code) from group_table;

Answer 1

You seem to want:

Distinct values for group_name_code and a sequential id ordered by minimum id per set of group_name_code.

Netezza has the DISTINCT key word, but not DISTINCT ON () (Postgres feature):
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_select.html

You could:

SELECT DISTINCT group_name_code FROM group_table;

No parentheses, the DISTINCT key word does not require parentheses.
But you would not get the sequential id you show with this.

There are "analytic functions" a.k.a. window functions:
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_overview_analytic_funcs.html

And there is also row_number():
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_functions.html

So this should work:

SELECT row_number() OVER (ORDER BY min(id)) AS new_id, group_name_code
FROM   group_table
GROUP  BY group_name_code
ORDER  BY min(id);

Or use a subquery if Netezza should not allow to nest aggregate and window functions:

SELECT row_number() OVER (ORDER BY id) AS new_id, group_name_code
FROM  (
   SELECT min(id) AS id, group_name_code
   FROM   group_table
   GROUP  BY group_name_code
   ) sub
ORDER  BY id;