We help IT Professionals succeed at work.

Custom order by

RanjeetRain
RanjeetRain asked
on
Medium Priority
1,616 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.


Comment
Watch Question

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;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.
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

Author

Commented:
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.
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)
);

i am wondering how the order is generated? just by the order you type them in?

Author

Commented:
Yes, that's what I want end result to look like.
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.
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
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

Author

Commented:
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.

Author

Commented:
Sorry about keeping this open so long. Thanks everyone for your suggestions.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.