Priority pick order 1

[chaoma]

Hello,

I need to pick records base on the status with the highest priority. For example,

ID Status
1 PN
1 AP
1 UD
2 PE
2 UD
3 AP

The highest priority order is AP, PN, UD.
I want my result to be:

ID Status
1 AP
2 PE
3 AP

So all ID must be select, but only the status with the highest priority leveL will be select.

Thank you for your assistance.

 

[r937]

your example shows PE being selected for 2

but the priority is AP,PN,UD

so the result for 2 should be UD, not PE

select p1.ID
     , p1.Status
  from PickPriority as p1
inner
  join PickPriority as p2
    on p1.ID = p2.ID
group
    by p1.ID
     , p1.Status
having case when p1.Status = 'AP'
            then 1
            when p1.Status = 'PN'
            then 2
            when p1.Status = 'UD'
            then 3
            else 99999
  = ( min(
       case when p2.Status = 'AP'
            then 1
            when p2.Status = 'PN'
            then 2
            when p2.Status = 'UD'
            then 3
            else 99999
         ) )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

select
  id, 
  coalesce(max(case when status = 'AP' then status end),
           max(case when status = 'PN' then status end),
           max(case when status = 'UD' then status end)
          )as status
from mytab
group by id;

If there are more columns and you need to select those:

select * from  mytab t1
where status = 
 (
  select
    coalesce(max(case when status = 'AP' then status end),
             max(case when status = 'PN' then status end),
             max(case when status = 'UD' then status end)
            )as status
from mytab
where id = t1.id)

If your DBMS already supports SQL:1999 OLAP functions:

select *
from
 (
  select 
    t1.*, 
    rank() over (partition by id 
                 order by case when status = 'AP' then status end,
                          case when status = 'PN' then status end,
                          case when status = 'UD' then status end
                ) as rnk
  from  mytab t1
 ) dt
where rnk = 1

Dieter

 

[r937]

dieter, that is sweet

nice job :star:

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[r937]

oh, wait...

in your first query, if Status is XY (i.e. not one of AP,PN,UD), the query should return XY, not null

that coalesce trick is still pretty impressive, though



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

Forgot about that, so simply add another column to the coalesce:

select
  id, 
  coalesce(max(case when status = 'AP' then status end),
           max(case when status = 'PN' then status end),
           max(case when status = 'UD' then status end),
[COLOR=red]           max(status)[/color] 
          )as status
from mytab
group by id;

Dieter

 

[chaoma]

Thank you for all your responses. That was a lot of responses.

status=PE on ID 2 was a mistype, I mean status=PN.

I am using SQL 2000.

I will try them when I go back to work on Monday.

Thanks.