Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order of IN operator Expand / Collapse
Author
Message
Posted Thursday, July 3, 2014 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:54 AM
Points: 6, Visits: 20
select empid from consumer where empid in (2323,1236,5623)
I want result would be of same order as i put in IN operator.see output

Output
empid
2323
1236
5623

Is this possible???

Post #1589085
Posted Thursday, July 3, 2014 12:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
atultiwari.eng06 (7/3/2014)
select empid from consumer where empid in (2323,1236,5623)
I want result would be of same order as i put in IN operator.see output

Output
empid
2323
1236
5623

Is this possible???



Not without having another column to use as your ordering column. Remember that IN is used to filter your rows. It has nothing to do with the order of the results. If you want ordered results there is 1 and only 1 way to do that, with an ORDER BY clause.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1589087
Posted Thursday, July 3, 2014 12:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,499, Visits: 7,549
Those are independent clauses.
You could work more to give the correct order.
SELECT empid 
FROM consumer
WHERE empid in (2323,1236,5623)
ORDER BY CASE WHEN empid = 2323 THEN 1
WHEN empid = 1236 THEN 2
WHEN empid = 5323 THEN 3
ELSE empid
END




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1589090
Posted Thursday, July 3, 2014 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:54 AM
Points: 6, Visits: 20
Luis Cazares u done great job...
Thanks a lot
Post #1589096
Posted Thursday, July 3, 2014 1:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 1,899, Visits: 18,901
just a thought and maybe provide flexibility if you require....

IF OBJECT_ID('tempdb..sort') IS NOT NULL DROP TABLE sort;

CREATE TABLE #sort
(sortID int not null,
sortvalue int not null);

INSERT #sort VALUES (1, 2323),(2, 1236),(3, 5623);

SELECT c.empid
FROM consumer AS c
INNER JOIN #sort AS s
ON c.empid = s.sortvalue
ORDER BY s.sortID




__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1589124
Posted Friday, July 4, 2014 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:54 AM
Points: 6, Visits: 20
I m using oracle sql developer...
any good solution for that??
Post #1589315
Posted Friday, July 4, 2014 6:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
Yeah, an Oracle forum. We're a MS SQL Server site, so solutions posted here are generally for SQL Server, especially since you never gave any hint you were working with Oracle (posted in a SQL Server 2008 forum)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1589333
Posted Friday, July 4, 2014 8:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,499, Visits: 7,549
atultiwari.eng06 (7/4/2014)
I m using oracle sql developer...
any good solution for that??

The solution that I posted? It will work on most RDBMS including Oracle.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1589368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse