July 3, 2014 at 12:05 pm
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???
July 3, 2014 at 12:09 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 12:12 pm
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
July 3, 2014 at 12:28 pm
Luis Cazares u done great job...
Thanks a lot
July 3, 2014 at 1:47 pm
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
and remember....every day is a school day
July 4, 2014 at 6:14 am
I m using oracle sql developer...
any good solution for that??
July 4, 2014 at 6:47 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2014 at 8:10 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply