SQL：将列B的相似值聚类，但将其“排序”按A栏 [英] SQL: Cluster similar values of column B, yet "order" by column A

问题描述

假设我有一个包含两列的表，分别为 id 和 hash ：

Let's say I have a table with two columns, id and hash:

id     | hash
------------------
1      | bb
2      | aa
3      | aa
4      | bb

我需要按 id （降序）对它们进行排序，但将所有分组具有相同值的行。可以做到这一点的算法例如：

I need to order them by id (descending), yet group all the rows that have the same value. An algorithm that did this would be for example:

• 收集到表的不同子集中，以使 hash 相同的列在一起。 li>
  
• 将子集的最大 id 降序排列。


• 子集行可以选择为按 id 降序排列。

• Gather into disjunct subsets the table so that the columns for which hash is the same are together.
• Sort the subsets by the their maximum id, descending.
• The subsets rows may optionally be sorted by id, descending.

结果为

id     | hash
------------------
4      | bb
1      | bb
3      | aa
2      | aa

推荐答案

在Postgres 8.4中...（按数字排序只是该列排序的别名）

In Postgres 8.4...(order by number is just an alias for the column to order by)

select id,hash, max(id) over (partition by hash) h
   from my_table order by 3 desc,1 desc;

OR

select id,hash
from my_table order by max(id) over (partition by hash) desc,
id desc

这篇关于SQL：将列B的相似值聚类，但将其“排序”按A栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！