[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Custom order by

Posted on 2006-04-04
13
Medium Priority
?
1,591 Views
Last Modified: 2008-01-09
This might be easy for you, but not for me. I have a query like:

SELECT s.section_name, F.PROMPT
FROM knta_sections s, KNTA_PARAMETER_SET_FIELDS F
WHERE F.Parameter_Set_Context_id IN (31274)
        AND f.section_id = s.section_id
        AND F.PROMPT IN ('Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:')


This returns me the desired output. However, the sorting order isn't great. I would like the output in the same order as the input list supplied to PROMPT. i.e.

'Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:'

Can you help? Thanks in advance.


0
Comment
Question by:RanjeetRain
12 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 1000 total points
ID: 16375088
Try:

SELECT s.section_name, F.PROMPT
FROM knta_sections s, KNTA_PARAMETER_SET_FIELDS F
WHERE F.Parameter_Set_Context_id IN (31274)
       AND f.section_id = s.section_id
       AND F.PROMPT IN ('Project Lead:', 'Budget:', 'Detailed CBA:', 'TG2 Revised CBA:')
  ORDER BY
       DECODE(F.PROMPT,'Project Lead:','0', 'Budget:','1', 'Detailed CBA:','2', 'TG2 Revised CBA:','3','4'),
       s.section_name;
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 16375170
Thanks for the help. However, the number of items in the list can be very big. How will it handle that situation. Also, the list may be a result of a subquery.
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 200 total points
ID: 16377201
where are those values coming from?  
You could have other choices depending on how you decide the order.

If none exists, you could build a temporary table to hold values as:
value             , order
'Project Lead',1
'Budget',2
'Detailed CBA',3
...

then you could do:

select ..
from ... ,temp
where
  AND F.PROMPT = temp.value
order by temp.order

Enjoy :)
Acton
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Author Comment

by:RanjeetRain
ID: 16377230
Good idea, actonwang. Thanks for the same. However, its seems too much of hassle to populate values in a temporary table and all. I would like to explore the possibility of getting this done by manually passing the values in the list in SQL itself, or as a result of a subquery. Thanks again.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16377289
no problem.

Actually temp table is not that difficult if your sql statement is inside an transaction. It will look more natural or error prone than decode.

use:

create global temporary table temp (
value varchar2(10),
order number(5)
);

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16377292
i am wondering how the order is generated? just by the order you type them in?
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 16377315
Yes, that's what I want end result to look like.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16377331
i'd use temp table. Draft a script to do this.

PL/SQL also can do this but it is overkill.

If less values invovled, use decode.
0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 200 total points
ID: 16378001
DECODE as suggested by MikeOM DBA is the best solution for a list of hardcoded values. If you are going to use a subquery, then all you have to do is NOT use the ORDER BY clause at all:

SCOTT@PROD>SELECT * FROM EMP_LIST;

ENAME
-----
JONES
ADAMS
SMITH
SCOTT

4 rows selected.

Elapsed: 00:00:00.01
SCOTT@PROD>SELECT ENAME FROM EMP WHERE ENAME IN( 'JONES','ADAMS','SMITH','SCOTT');  -- Order comes from emp table

ENAME
----------
SMITH
JONES
SCOTT
ADAMS

4 rows selected.

Elapsed: 00:00:00.00
SCOTT@PROD>SELECT ENAME FROM EMP WHERE ENAME IN( SELECT ENAME FROM EMP_LIST); -- Order comes from subquery

ENAME
----------
JONES
ADAMS
SMITH
SCOTT
0
 
LVL 2

Assisted Solution

by:abbas_najafizadeh
abbas_najafizadeh earned 200 total points
ID: 16389423
SELECT s.section_name, F.PROMPT
FROM knta_sections s, KNTA_PARAMETER_SET_FIELDS F
,(select 'Project Lead:' as Title,1 as T_Order
  from dual
union
  select 'Budget:',2 from dual
union
  select 'Detailed CBA:',3 from dual
union
 select 'TG2 Revised CBA:',4 from dual
) MyTitleOrder
WHERE F.Parameter_Set_Context_id IN (31274)
       AND f.section_id = s.section_id
       AND F.PROMPT=MyTitleOrder.Title
order by MyTitleOrder.T_Order
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 16391976
Good ideas. So far MikeOM's idea looks the best. Let me see if someone can better that. Otherwise I'll close. Thanks for the help.
0
 
LVL 19

Author Comment

by:RanjeetRain
ID: 17448955
Sorry about keeping this open so long. Thanks everyone for your suggestions.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question