September 26, 2013 at 5:26 pm
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?
September 26, 2013 at 6:58 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 26, 2013 at 7:32 pm
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.
September 26, 2013 at 8:00 pm
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[/url] can be found at the link.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 8:11 am
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 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/
September 27, 2013 at 12:46 pm
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
September 27, 2013 at 10:12 pm
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.
September 29, 2013 at 6:09 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply