4

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

Improve this question
13
  • 1
    Do a GROUP BY on the ID column. Commented Jan 19, 2016 at 10:47
  • 2
    @jarlh And how would he display the Amount, based on the grouped ID values? Commented Jan 19, 2016 at 10:48
  • @RaduGheorghiu, good question, I can't see any pattern in how the selected values are chosen. Up to OP. (Or do you have any idea?) Commented Jan 19, 2016 at 10:59
  • 1
    Individual columns must be unique so either AA 10 or AA 20 could be chosen as long as AA isn't already chosen and (10 or 20) haven't already been chosen. it's stated that selection order does not matter. (edit sorry was late hitting enter) Commented Jan 19, 2016 at 12:11
  • 1
    What if you only have AA 10 and BB 10??? Commented Jan 19, 2016 at 13:24

5 Answers 5

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The problem is easier if you can "break" the row. I mean, combination EE 50 does not exists in the input data. The problem as it appears in the OP, is very dificult. It may not have solution for some inputs, it may have many solution for other inputs.
@FlorinGhita: ah, good point. I didn't understand it like that. I think the requirement "each value must be unique" can't be satisfied, if you aren't allowed to "break up" the rows.
However, I will vote this answer to bring the issue up. ;)
0

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.

Improve this answer

2 Comments

It didn't work properly unfortunately, I still get repeating values in Amount column. Thank you for your time
sorry, I missed the significance of those words, but now I understand what you need. mmmm
0

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

Improve this answer

1 Comment

The same thing as in @Aleksej case: query time is extremely large and I simply cannot wait until it finishes
0

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;
Improve this answer

1 Comment

It's elegant, but incorrect :) Try combination: (BB, 30), (BB, 40), (CC, 30), (CC, 40) My solution is the only solution, which gives correct answer with this combination (In fact, such combinations could be found for my solution too, but this solution is most weak).
0

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
Improve this answer

3 Comments

I honestly tried to execute this query, but didn't manage as SQL Developer cannot finish query within even 5 minutes, which is too much. Thank you for your effort
LEFT JOIN tx tx2 ON tx1.id = tx2.id(+) looks terribly wrong. You should use either a proper left join or Oracle's proprietary (+) operator but not both in the same join condition.
ops.. my error. The operator (+) is wrong, just removed. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.