Home » SQL & PL/SQL » SQL & PL/SQL » Ordering question
Ordering question [message #200825] Wed, 01 November 2006 08:33 Go to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
I have a view. Included in this view are the fields id, mc_ef, p_flg and r_typ.

p_flg contains the values 'P', 'A' or NULL.
mc_ef contains the values 'Sxxx', 'Lxxx' and 'Cxxx' where 'xxx' is a 3 digit number.
r_typ contains the values 'I', 'U', 'C' or NULL.

You will find multiple records for each ID containing any combination of the above.

What I want to do is select the first record of each ID sorted by p_flg, mc_ef and r_typ in that order (and sort p_flg, mc_ef and r_typ in the order in which I listed above)

So for simplicities sake, let's say I have the following records:

ID / p_flg / mc_ef / r_typ
100 / P / L403 / C
100 / A / S566 / I
100 / A / C033 / I
101 / A / L330 / U
101 / A / S220 / I
102 / P / C002 / C
102 / P / S110 / C
102 / P / S339 / I
102 / A / S330 / I

I want my query to select:

100 / P / L403 / C (because p_flg = P and the others were A)
101 / A / S220 / I (because there were no p_flg = P and because mc_ef = Sxxx)
102 / P / S339 / I (because there were multiple p_flg = P and multiple mc_ef = Sxxx and the r_typ = I)

I have the following query:

SELECT id, mc_ef, p_flg, r_typ
from test_view
order by id,
  case p_flg
  when 'P' then 1
  when 'A' then 2
  else 3
  end, 
  case substr(mc_ef,1,1)
  when 'S' then 1
  when 'L' then 2
  when 'C' then 3
  end,
  case r_typ
  when 'I' then 1
  when 'U' then 2
  when 'C' then 3
  else 4
  END


This query would then display my data above in the following order:
100 / P / L403 / C*
100 / A / S566 / I
100 / A / C033 / I
101 / A / S220 / I*
101 / A / L330 / U
102 / P / S339 / I*
102 / P / S110 / C
102 / P / C002 / C
102 / A / S330 / I

What I would then want to do is select the (*) records, which are the first instances of each id.

Does anyone have any idea how I can change my current query to pull out only those records?

Thanks!

[Updated on: Wed, 01 November 2006 08:38]

Report message to a moderator

Re: Ordering question [message #200829 is a reply to message #200825] Wed, 01 November 2006 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is a bit messy to look at, but it works

select distinct id
      ,first_value(p_flg) over (partition by id order by case p_flg  when 'P' then 1  when 'A' then 2  else 3  end
                                                        ,case substr(mc_ef,1,1)  when 'S' then 1  when 'L' then 2  when 'C' then 3  end
                                                        ,case r_typ  when 'I' then 1  when 'U' then 2  when 'C' then 3  else 4  END)
     ,first_value(mc_ef) over (partition by id order by case p_flg  when 'P' then 1  when 'A' then 2  else 3  end
                                                       ,case substr(mc_ef,1,1)  when 'S' then 1  when 'L' then 2  when 'C' then 3  end
                                                       ,case r_typ  when 'I' then 1  when 'U' then 2  when 'C' then 3  else 4  END)      
     ,first_value(r_typ) over (partition by id order by case p_flg  when 'P' then 1  when 'A' then 2  else 3  end
                                                       ,case substr(mc_ef,1,1)  when 'S' then 1  when 'L' then 2  when 'C' then 3  end
                                                       ,case r_typ  when 'I' then 1  when 'U' then 2  when 'C' then 3  else 4  END)      
from   temp_order;      
Re: Ordering question [message #200834 is a reply to message #200829] Wed, 01 November 2006 09:26 Go to previous messageGo to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
Thanks! It appears to work just fine.

Like you said it is a little messy, so if someone else has a better solution I am still open to suggestions. But this solution should work.

Thanks again!
Re: Ordering question [message #200849 is a reply to message #200834] Wed, 01 November 2006 11:05 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This any "cleaner" for you :

select id, p_flg, mc_ef, r_typ from (
	select id, p_flg, mc_ef, r_typ,
	dense_rank() over (partition by id order by decode(p_flg, 'P', 1, 'A', 2, 3),
	decode(substr(mc_ef,1,1), 'S', 1, 'L', 2, 'C', 3, 4),
	decode(r_typ, 'I', 1, 'U', 2, 'C', 3, 4) asc) rnk from test_view)
where rnk = 1;
Re: Ordering question [message #200883 is a reply to message #200849] Wed, 01 November 2006 14:56 Go to previous messageGo to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
This works as well...thanks!
Re: Ordering question [message #200975 is a reply to message #200834] Thu, 02 November 2006 01:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or this
SELECT * FROM (
SELECT id
      ,mc_ef
      ,p_flg
      ,r_typ
      ,row_number() over (partition by id order by case p_flg  when 'P' then 1  when 'A' then 2  else 3  end, 
      case substr(mc_ef,1,1)  when 'S' then 1  when 'L' then 2  when 'C' then 3  end,
      case r_typ  when 'I' then 1  when 'U' then 2  when 'C' then 3  else 4 END) rnum
from temp_order)
WHERE rnum=1;


[Actually, that's more similar to @ebrians solution than it looks. Hey ho.]

[Updated on: Thu, 02 November 2006 01:54]

Report message to a moderator

Previous Topic: Using Distinct and Upper in Select statement
Next Topic: Validate a Field
Goto Forum:
  


Current Time: Fri Dec 09 09:47:06 CST 2016

Total time taken to generate the page: 0.16466 seconds