CODEWITHSUNDEEP
sqloracle-database
Anton
Anton

Reputation: 461

How can I select unique values from several columns in Oracle SQL?

Basically, I've got the following table:

ID | Amount
AA | 10
AA | 20
BB | 30
BB | 40
CC | 10
CC | 50
DD | 20
DD | 60
EE | 30
EE | 70

I need to get unique entries in each column as in following example:

ID | Amount
AA | 10
BB | 30
CC | 50
DD | 60
EE | 70

So far following snippet gives almost what I wanted, but first_value() may return some value, which isn't unique in current column:

first_value(Amount) over (partition by ID)

Distinct also isn't helpful, as it returns unique rows, not its values

EDIT: Selection order doesn't matter

Upvotes: 4

Views: 2053

Answers (5)

Aleksej
Aleksej

Reputation: 22949

Maybe something like this can solve:

WITH tx AS
     (  SELECT ROWNUM ROW_NUMBER,
               t.id,
               t.amount
          FROM test t
               INNER JOIN test t2
                   ON     t.id = t2.id
                      AND t.amount != t2.amount
      ORDER BY t.id)
SELECT tx1.id, tx1.amount
  FROM tx tx1
   LEFT JOIN tx tx2
       ON     tx1.id = tx2.id
          AND tx1.ROW_NUMBER > tx2.ROW_NUMBER
WHERE tx2.ROW_NUMBER IS NULL

Upvotes: 0

user330315
user330315

Reputation:

This works for me, even with the problematic combinations mentioned by Dimitri. I don't know how fast that is for larger volumes though

with ids as (
  select id, row_number() over (order by id) as rn
  from data
  group by id
), amounts as (
  select amount, row_number() over (order by amount) as rn
  from data
  group by amount
)
select i.id, a.amount
from ids i
  join amounts a on i.rn = a.rn;

SQLFiddle currently doesn't work for me, here is my test script:

create table data (id varchar(10), amount integer);

insert into data values ('AA',10);
insert into data values ('AA',20);
insert into data values ('BB',30);
insert into data values ('BB',40);
insert into data values ('CC',10);
insert into data values ('CC',50);
insert into data values ('DD',20);
insert into data values ('DD',60);
insert into data values ('EE',30);
insert into data values ('EE',70);

Output:

id | amount
---+-------
AA |     10
BB |     20
CC |     30
DD |     40
EE |     50

Upvotes: 2

Anton
Anton

Reputation: 461

I knew that there is an elegant solution! Thanks to friend of mine for a tip:

select max(ID), mAmount from (
  select ID, max(Amount) mAmount from table group by ID
)
group by mAmount;

Upvotes: 0

Dmitriy
Dmitriy

Reputation: 5565

My solution implements recursive with and makes following: first - select minival values of ID and amount, then for every next level searches values of ID and amount, which are more than already choosed (this provides uniqueness), and at the end query selects 1 row for every value of recursion level. But this is not an ultimate solution, because it is possible to find a combination of source data, where query will not work (I suppose, that such solution is impossible, at least in SQL).

with r (id, amount, lvl) as (select min(id), min(amount), 1
             from t
            union all
           select t.id, t.amount, r.lvl + 1
             from t, r
            where t.id > r.id and t.amount > r.amount)
select lvl, min(id), min(amount)
  from r
 group by lvl
 order by lvl

SQL Fiddle

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

I suggest using row_number() like this:

select ID ,Amount
from (
   select ID ,Amount, row_number() over(partition by id order by 1) as rn
   from yourtable
     )
where rn = 1

However your expected results don't conform to a discrenable order, some are the first/lowest while some the last/highest so I wasn't sure what to include for the ordering.

Upvotes: 0

Related Questions