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 BY clause based on values IN () Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 5:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
Hi all,

So my client want to run a SQL query to list data along the lines of
SELECT field1, field2, field3 FROM table WHERE field1 IN (83944, 83955, 83954, 83951,83947, 83946, 83953)

The output needs to be in the same order that the numbers are in the IN clause, in other words
83944, field2, field3
83955,field2, field3
83954, field2, field3
83951, field2, field3
83947, field2, field3
83946, field2, field3
83953, field2, field3

There is no other ordering based on another field possible. The client is manually typing in those numbers into the IN in the specific order he wants the output.
Is there some way that SQL knows the order of each element in the IN brackets?

I know I could write some fairly simple T-SQL with a temp table and an identity field. Then do an INSERT INTO #TempTable.
SELECT * FROM #TempTable ORDER BY IdentityField

But looking for another way maybe?
Post #1499112
Posted Thursday, September 26, 2013 6:58 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: 2 days ago @ 5:45 PM
Points: 3,617, Visits: 5,237
How about this?

SELECT field1, field2, field3 
FROM table
WHERE field1 IN (83944, 83955, 83954, 83951,83947, 83946, 83953)
ORDER BY CASE field1
WHEN 83944 THEN 1
WHEN 83955 THEN 2
WHEN 83954 THEN 3
WHEN 83951 THEN 4
WHEN 83947 THEN 5
WHEN 83946 THEN 6
WHEN 83953 THEN 7
END





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499129
Posted Thursday, September 26, 2013 7:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
But that is assuming the numbers are static. What if they change all the time or any time the query is entered? I guess the user could enter his values twice, that might be ok.
Post #1499137
Posted Thursday, September 26, 2013 8:00 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: 2 days ago @ 5:45 PM
Points: 3,617, Visits: 5,237
I was assuming you dynamically built the query in the front end so could handle splitting the items that are part of the IN clause.

Alternatively, something like this will also work.

DECLARE @UsersEntry VARCHAR(8000) = '83944, 83955, 83954, 83951,83947, 83946, 83953';

WITH UserParms AS
(
SELECT ItemNumber, Item
FROM dbo.DelimitedSplit8K(@UsersEntry, ',')
)
SELECT field1, field2, field3
FROM table
JOIN UserParms ON field1 = item
WHERE field1 IN (SELECT Item FROM UserParms)
ORDER BY ItemNumber;


DelimitedSplit8K can be found at the link.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499141
Posted Friday, September 27, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 13,139, Visits: 11,980
Dwain, I think this should produce the same result?

SELECT field1, field2, field3 
FROM table
JOIN dbo.DelimitedSplit8K(@UsersEntry, ',') s ON field1 = s.item
ORDER BY s.ItemNumber;



_______________________________________________________________

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 #1499407
Posted Friday, September 27, 2013 12:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 984, Visits: 1,325
I'd probably do something silly like this:
declare @somedata table (ID int primary key identity (1,1)
,field1 int
)
insert into @somedata (field1) values (83944)
insert into @somedata (field1) values (83955)
insert into @somedata (field1) values (83954)
insert into @somedata (field1) values (83951)
insert into @somedata (field1) values (83947)
insert into @somedata (field1) values (83946)
insert into @somedata (field1) values (83953)

select tbl.field1
, tbl.field2
, tbl.field3
FROM table tbl inner join
@somedata sd on tbl.field1 = sd.field1
order by sd.ID

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1499552
Posted Friday, September 27, 2013 10:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 5:35 PM
Points: 46, Visits: 74
All good responses.
In the end I went with Dwain's first suggestion. Slight variation.

SELECT TOP 7 field1, field2, field3
FROM table
ORDER BY CASE field1
WHEN 83944 THEN 1
WHEN 83955 THEN 2
WHEN 83954 THEN 3
WHEN 83951 THEN 4
WHEN 83947 THEN 5
WHEN 83946 THEN 6
WHEN 83953 THEN 7
ELSE 100
END

That works a treat for me. I was going to go with Kurt's idea (my original thought anyway) but the above is simple & kinda elegant.

Thanks all for the responses.
Post #1499633
Posted Sunday, September 29, 2013 6:09 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: 2 days ago @ 5:45 PM
Points: 3,617, Visits: 5,237
Sean Lange (9/27/2013)
Dwain, I think this should produce the same result?

SELECT field1, field2, field3 
FROM table
JOIN dbo.DelimitedSplit8K(@UsersEntry, ',') s ON field1 = s.item
ORDER BY s.ItemNumber;



I believe that it most certainly would!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse