postgresql - SQL: Cluster similar values of column B, yet "order" by column A

Question

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

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

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:

• 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.

The result would be

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

Thanks.

Answer 1

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