How to get distinct sets of rows using SQL (Oracle)? 1

How to get distinct sets of rows using SQL (Oracle)?

WITH hashes AS
(SELECT group,
        SUM(Ora_hash(Concat(Concat(account,field), value))) AS hash
 FROM table
 GROUP BY group)
SELECT account,
       field,
       value,
       group
FROM table
WHERE group IN (SELECT group
                FROM (SELECT group,
                             row_number() over (PARTITION BY hash ORDER BY NULL) AS rn
                      FROM hashes)
                WHERE  rn = 1);

Here is what the above code is Doing:
1. The first subquery is calculating the hash of the concatenation of the account, field, and value columns for each group.
2. The second subquery is selecting the group with the lowest hash value for each hash value.
3. The outer query is selecting the rows from the original table that match the groups from the second subquery.

Similar Posts