CODEWITHSUNDEEP
sqloracle-databaseplsql
Qudus
Qudus

Reputation: 1520

Return distinct rows based on only one column in oracle sql

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}"

Upvotes: 0

Views: 688

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6064

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)
  --Above is to set up the sample data. Use the query below with your real table
  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
   FETCH FIRST 4 ROWS ONLY;

Result

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

Upvotes: 1

DB08
DB08

Reputation: 181

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

Upvotes: 1

Related Questions