Custom order by

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.


LVL 19
RanjeetRainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RanjeetRainAuthor 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.
0
actonwangCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RanjeetRainAuthor 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.
0
actonwangCommented:
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
actonwangCommented:
i am wondering how the order is generated? just by the order you type them in?
0
RanjeetRainAuthor Commented:
Yes, that's what I want end result to look like.
0
actonwangCommented:
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
paquicubaCommented:
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
abbas_najafizadehCommented:
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
RanjeetRainAuthor 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.
0
RanjeetRainAuthor Commented:
Sorry about keeping this open so long. Thanks everyone for your suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.