SQL在Netezza DB中获取唯一行 [英] SQL to get unique rows in Netezza DB

问题描述

我有一个表，其中包含诸如:

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  

此查询有帮助吗?

select id, distinct(group_name_code) from group_table;

推荐答案

您似乎想要:

group_name_code和连续id的不同值，按每个group_name_code组的最小ID排序.

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

Netezza具有DISTINCT关键字，但没有 DISTINCT ON ()(Postgres功能):
https://www.ibm.com/support/knowledgecenter/zh-CN/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_select.html

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

您可以:

SELECT DISTINCT group_name_code FROM group_table;

没有括号，DISTINCT关键字不需要括号.
但是您不会得到与此显示的顺序id.

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

有解析函数"又称窗口函数:
https://www.ibm.com/support/knowledgecenter/zh-CN/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_overview_analytic_funcs.html

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

还有row_number():
https://www.ibm.com/support/knowledgecenter/zh-CN/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_functions.html

所以这应该起作用:

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);

或者如果Netezza不允许嵌套聚合和窗口函数，则使用子查询:

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;

这篇关于SQL在Netezza DB中获取唯一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！