Return distinct rows based on only one column in oracle sql-CodePudding

I want to return an n number of distinct rows. The distinct rows should be based on one column (SN) only.

I have the query below which is expected to return 4 rows where the serial number is greater than 2 and no rows with similar SN column values are returned.

Table

SN  letter value
 1   test   25
 1   bread  26
 3   alpha  43
 4   beta   23
 4   gamma  5
 5   omega  60
 6   omega  60

Expected Result

SN  letter value
 3   alpha  43
 4   beta   23
 5   omega  60
 6   omega 60

This is the query I have. This does not work correctly, it returns the duplicates because it filters disctinct values by all the columns combined instead of just the single column, SN.

SELECT * FROM (SELECT a.*, row_number() over(order by SN) rowRank 
FROM (SELECT distinct SN, letter, value from table where SN > 2 order by SN) a) 
WHERE rowRank BETWEEN 1 AND 4}"

CodePudding user response：

You do not need to use DISTINCT before trying to filter out your results. You can modify the ORDER BY clause of the row_rank analytic function if you need to modify which duplicate of a SN should be returned. Right now it is returning the first LETTER value alphabetically since that matches your example result.

Query

WITH
    some_table (sn, letter, VALUE)
    AS
        (SELECT 1, 'test', 25 FROM DUAL
         UNION ALL
         SELECT 1, 'bread', 26 FROM DUAL
         UNION ALL
         SELECT 3, 'alpha', 43 FROM DUAL
         UNION ALL
         SELECT 4, 'beta', 23 FROM DUAL
         UNION ALL
         SELECT 4, 'gamma', 5 FROM DUAL
         UNION ALL
         SELECT 5, 'omega', 60 FROM DUAL
         UNION ALL
         SELECT 6, 'omega', 60 FROM DUAL)
  SELECT sn,
         letter,
         VALUE
    FROM (SELECT sn,
                 letter,
                 VALUE,
                 ROW_NUMBER () OVER (PARTITION BY sn ORDER BY letter)     AS row_rank
            FROM some_table
           WHERE sn > 2)
   WHERE row_rank = 1
ORDER BY sn;

Result

   SN    LETTER    VALUE
_____ _________ ________
    3 alpha           43
    4 beta            23
    5 omega           60
    6 omega           60

CodePudding user response：

SELECT * FROM 
    (
        SELECT 
        t.* 
        ,ROW_NUMBER() OVER (PARTITION BY sn ORDER BY sn,value ) rn
        FROM 
        t
        WHERE sn > 2
    ) t1
 WHERE t1.rn = 1
 ORDER BY sn;