SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORDER BY clause based on values IN ()


ORDER BY clause based on values IN ()

Author
Message
Danster
Danster
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 90
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?
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18039 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Danster
Danster
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 90
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.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18039 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63334 Visits: 17965
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 Modens 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)
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2646 Visits: 1400
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
Danster
Danster
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 90
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.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18039 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search