Home » SQL & PL/SQL » SQL & PL/SQL » Order by for a specific category (Oracle 10g)
Order by for a specific category [message #409598] Tue, 23 June 2009 04:37 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to display some records in order for a specific category.
SELECT G.ACCESS_ID accId ,ACCESS_RES accRes, substr(G.ACCESS_NAME,5) name , G.ACCESS_DESC_ID descid FROM MEMS_ACCESS G,
 MEMS_ROLE B, MEMS_ACCESS_DESC C WHERE B.ROLENAME='SysUser' AND G.ACCESS_ID= B.ACCESS_ID
  AND G.ACCESS_DESC_ID=C.ACCESS_DESC_ID


I have to display the records such that I get the records order by "name" only for the ACCESS_DESC_ID = 3 and the rest records should get displayed in their normal order as in database.

I tried using UNION and order by for required condition only but still I am not getting the required order.

select GDF.accId,GDF.accRes,GDF.name ,GDF.descid     from 
(SELECT G.ACCESS_ID accId ,ACCESS_RES accRes, substr(G.ACCESS_NAME,5) name , G.ACCESS_DESC_ID descid FROM MEMS_ACCESS G,
 MEMS_ROLE B, MEMS_ACCESS_DESC C WHERE B.ROLENAME='SysUser' AND G.ACCESS_ID= B.ACCESS_ID
  AND G.ACCESS_DESC_ID=C.ACCESS_DESC_ID and G.ACCESS_DESC_ID = 3 
ORDER BY substr(G.ACCESS_NAME,5))  GDF
union 
SELECT A.ACCESS_ID,ACCESS_RES, substr(A.ACCESS_NAME,5), A.ACCESS_DESC_ID descid FROM MEMS_ACCESS A,
 MEMS_ROLE B, MEMS_ACCESS_DESC C WHERE B.ROLENAME='SysUser' AND A.ACCESS_ID= B.ACCESS_ID
  AND A.ACCESS_DESC_ID=C.ACCESS_DESC_ID and A.ACCESS_DESC_ID <> 3


Please suggest a way to do so.

Regards,
Mahi
Re: Order by for a specific category [message #409602 is a reply to message #409598] Tue, 23 June 2009 04:51 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

I have to display the records such that I get the records order by "name" only for the ACCESS_DESC_ID = 3 and the rest records should get displayed in their normal order as in database.


There is no order in the database. I think this is what you are after
select ....
from ....
order  by case when access_desc_id = 3 then name else null end

From next time if you could post some sample data along with the expected output it will avoid lots of ambiguity and assumption, moreover you will get a quick reply.

Regards

raj
Previous Topic: Function
Next Topic: How to devide a string in to sub strings
Goto Forum:
  


Current Time: Mon Dec 05 19:07:47 CST 2016

Total time taken to generate the page: 0.06403 seconds