CODEWITHSUNDEEP
sqloracle-database
user1016403
user1016403

Reputation: 12621

Get distinct rows based on priority?

I have a table as below.i am using oracle 10g.

TableA
------
id  status
---------------
1   R
1   S
1   W
2   R

i need to get distinct ids along with their status. if i query for distinct ids and their status i get all 4 rows. but i should get only 2. one per id. here id 1 has 3 distinct statuses. here i should get only one row based on priority.

first priority is to 'S' , second priority to 'W' and third priority to 'R'.

in my case i should get two records as below.

id  status
--------------
1   S
2   R

How can i do that? Please help me.

Thanks!

Upvotes: 4

Views: 3685

Answers (7)

Art
Art

Reputation: 5782

Using MOD() example with added values:

SELECT id, val, distinct_val
  FROM
  (
  SELECT id, val
       , ROW_NUMBER() OVER (ORDER BY id) row_seq
       , MOD(ROW_NUMBER() OVER (ORDER BY id), 2) even_row
       , (CASE WHEN id = MOD(ROW_NUMBER() OVER (ORDER BY id), 2) THEN NULL ELSE val END) distinct_val
   FROM
   (
   SELECT 1 id, 'R' val FROM dual
    UNION
   SELECT 1 id, 'S' val FROM dual
    UNION
   SELECT 1 id, 'W' val FROM dual
    UNION
   SELECT 2 id, 'R' val FROM dual
    UNION                          -- comment below for orig data
   SELECT 3 id, 'K' val FROM dual
    UNION
   SELECT 4 id, 'G' val FROM dual
    UNION
   SELECT 1 id, 'W' val FROM dual
  ))
  WHERE distinct_val IS NOT NULL
 /

ID    VAL    DISTINCT_VAL
--------------------------
1      S      S
2      R      R
3      K      K
4      G      G

Upvotes: 0

ranit b
ranit b

Reputation: 51

Something like this???

SQL> with xx as(
  2      select 1 id, 'R' status from dual UNION ALL
  3      select 1, 'S' from dual UNION ALL
  4      select 1, 'W' from dual UNION ALL
  5      select 2, 'R' from dual
  6  )
  7  select
  8      id,
  9      DECODE(
 10          MIN(
 11              DECODE(status,'S',1,'W',2,'R',3)
 12           ),
 13      1,'S',2,'W',3,'R') "status"
 14  from xx
 15  group by id;

        ID s
---------- -
         1 S
         2 R

Here, logic is quite simple. Do a DECODE for setting the 'Priority', then find the MIN (i.e. one with Higher Priority) value and again DECODE it back to get its 'Status'

Upvotes: 0

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

select id , status  from (         
select TableA.*, ROW_NUMBER()
OVER (PARTITION BY TableA.id  ORDER BY DECODE(
         TableA.status,
         'S',1,
         'W',2,
         'R',3,
             4)) AS row_no
FROM TableA) 
where row_no = 1

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

select
  id,
  max(status) keep (dense_rank first order by instr('SWR', status)) as status
from TableA
group by id
order by 1

fiddle

Upvotes: 4

Aspirant
Aspirant

Reputation: 2278

 select id,status from 
 (select id,status,decode(status,'S',1,'W',2,'R',3) st from table) where (id,st) in
 (select id,min(st) from (select id,status,decode(status,'S',1,'W',2,'R',3) st from table))

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

To get it done you can write a similar query:

     -- sample of data from your question 
SQL> with t1(id , status) as (
  2    select 1,   'R'  from dual union all
  3    select 1,   'S'  from dual union all
  4    select 1,   'W'  from dual union all
  5    select 2,   'R'  from dual
  6  )
  7  select id   -- actual query
  8       , status
  9    from ( select id
 10                , status
 11                , row_number() over(partition by id
 12                                        order by case
 13                                                   when upper(status) = 'S'
 14                                                   then 1
 15                                                   when upper(status) = 'W'
 16                                                   then 2
 17                                                   when upper(status) = 'R'
 18                                                   then 3
 19                                                 end
 20                                     ) as rn
 21            from t1
 22         ) q
 23  where q.rn = 1
 24  ;

        ID STATUS
---------- ------
         1 S
         2 R

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

This is first thing i would do, but there may be a better way.

Select id, case when status=1 then 'S' 
                when status=2 then 'W'
                when status=3 then 'R' end as status
from(
    select id, max(case when status='S' then 3
                        when status='W' then 2
                        when status='R' then 1
                    end) status
    from tableA
    group by id
    );

Upvotes: 0

Related Questions