Home » SQL & PL/SQL » SQL & PL/SQL » 'IN' operator problem
'IN' operator problem [message #414462] Wed, 22 July 2009 05:00 Go to next message
harrryrk@gmail.com
Messages: 3
Registered: July 2009
Junior Member

I have a table called emp with ID's 1 to 1000 say.

I wrote a Query to get the emp details like

select * from emp where empid IN (3,16,19,2,15,4,7,1)

which produces the Output in the sorted order like 1,2,3,4,7,15,16,19

But I want the Output as in the order which I've mentioned after IN operator.

Is there any way to get the output as I needed?

Thanks,
harry
Re: 'IN' operator problem [message #414465 is a reply to message #414462] Wed, 22 July 2009 05:13 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
It's not possible to specify the order in IN clause of SQL. If you want to get the data in order of some field value, you can acheive this by using ORDER BY. For e.g. if you want to retrieve the employees in ascending order of age use ORDER BY age
Re: 'IN' operator problem [message #414468 is a reply to message #414462] Wed, 22 July 2009 05:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
In order to guarantee rows returned in a specific order, you MUST include an ORDER BY Clause, which makes the statement
Quote:
which produces the Output in the sorted order like 1,2,3,4,7,15,16,19

not quite true. The order in which the data is returned is random, it just happens to be in the order that you have shown
You could try:
ORDER BY DECODE(empid, 3,1, 16,2, 19,3, 2,4, 15,5, 4,6, 7,7, 1,8)
Re: 'IN' operator problem [message #414837 is a reply to message #414468] Thu, 23 July 2009 14:26 Go to previous messageGo to next message
kumar29
Messages: 6
Registered: July 2009
Junior Member
The Order by clause take care about the order in which it has to display data.
we can get the result in the way what you expected through a PL/SQL block. let me know if you would like to know how to write this plsql block.
Re: 'IN' operator problem [message #414838 is a reply to message #414837] Thu, 23 July 2009 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using a PL/SQL block when you can do in SQL?

Regards
Michel
Re: 'IN' operator problem [message #414844 is a reply to message #414462] Thu, 23 July 2009 15:34 Go to previous messageGo to next message
kumar29
Messages: 6
Registered: July 2009
Junior Member
I Agree with you. If we can write in SQL then no need to go for the PL/SQL block. Could you please advice how to get the expected order result by using a SQL statment.
I didn't try with the Decode option.Might be it will work.I belive if we use the decode function in order by clause it will reduce the performance. Please advice.

Thanks.
Re: 'IN' operator problem [message #414847 is a reply to message #414844] Thu, 23 July 2009 15:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, ANY method where you specify an "order" you like at a row-by-row basis with not apparent logic behind it will definitely reduce performance.

So your best bet would be to go with the decode.
Re: 'IN' operator problem [message #414879 is a reply to message #414844] Thu, 23 July 2009 23:17 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I didn't try with the Decode option.Might be it will work.

Yes, it will.

Regards
Michel
Previous Topic: Help me with this query
Next Topic: Calling PL/SQL Procedure
Goto Forum:
  


Current Time: Sat Dec 03 11:57:25 CST 2016

Total time taken to generate the page: 0.28250 seconds