Removing duplicate rows in select query

Question

I have data like below. I want to select only one task_id with higher priority.

TASK_ID : 13912 = 2 rows
          13913 = 3 rows

Task_ID owner   priority
13912   JUNGIL  99
13912   BANCS   1
13913   JUNGIL  99
13913   BANCS   1
13913   AIL     2

There much more data but I have to select only one.

possible duplicate of [Duplicate rows in Oracle](http://stackoverflow.com/questions/816851/duplicate-rows-in-oracle) — morten.c, Jul 31 '14 at 16:05

score 0 · Answer 1 · edited May 23 '17 at 12:20

Some badly needed practice with keep ... dense_rank first (thanks for the reminder Gordon Linoff):

select task_id,
  max(owner) keep (dense_rank last order by priority) as owner,
  max(priority) keep (dense_rank last order by priority) as priority
from t42
group by task_id
order by task_id;

   TASK_ID OWNER        PRIORITY
---------- ---------- ----------
     13912 JUNGIL             99 
     13913 JUNGIL             99

Or if the highest priority is 1 then:

select task_id,
  max(owner) keep (dense_rank first order by priority) as owner,
  max(priority) keep (dense_rank first order by priority) as priority
from t42
group by task_id
order by task_id;

   TASK_ID OWNER        PRIORITY
---------- ---------- ----------
     13912 BANCS               1 
     13913 BANCS               1

You could also do it with a subquery and analytics:

select task_id, owner, priority
from (
  select task_id, owner, priority,
    dense_rank() over (partition by task_id order by priority) as rnk
  from t42
)
where rnk = 1
order by task_id;

   TASK_ID OWNER        PRIORITY
---------- ---------- ----------
     13912 BANCS               1 
     13913 BANCS               1

... but the keep approach is simpler. SQL Fiddle showing both.

score 0 · Answer 2 · answered Jul 31 '14 at 17:17

Looks like you need just a group by query like this:

select max(priority), task_id
  from <YOUR_TABLE_HERE>
 group by t.task_id

if you want to have all the row's columns

select t.*
  from t
  join (
    select max(priority) priority, task_id
      from t
     group by t.task_id
  )t2 on t.task_id = t2.task_id and t.priority = t2.priority

Removing duplicate rows in select query

2 Answers2