ORDER BY, ID as string comma - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ORDER BY, ID as string comma

Author

Topic

skillilea
Starting Member

15 Posts

Posted - 2012-02-27 : 16:28:09

I have a field that holds an order by string of ID's. What is the best way to get the result set.

field of ID's: DECLARE @orderBY = "1, 3, 8, 10"

DATA
ID VALUE
1 xxxxX
2 xxxxY
3 xxxxZ
4 xxxxA
5 xxxxB
6 xxxxC
7 xxxxD
8 xxxxE
...

SELECT *
FROM table
ORDER BY orderBY

I thought about a temp table looping through putting them in and then returning...

I need all the records from the table back, the @orderBY will only be a subset they just need to come first in the list.

tnx tons

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-27 : 17:13:32

I'm not totally sure I understand the question, but I recently wrote a blog post that might be addressing this issue.

See: Parsing and normalizing string data:
http://practicaltsql.net/2012/02/24/parsing-string-data/

Let me know if this addresses your issue...

skillilea
Starting Member

15 Posts

Posted - 2012-02-27 : 20:10:40

Thanks for the post. I was thinking about splitting the @orderBy and then creating a #temp table or something similar to your CTE and join to get the result.

Let me be clear. I have a table of metrics that need to come back in the order by designated in the comma seperated string.

SO:
DECLARE
@orderBy as varchar(200)

SET @orderBy = "1,3,8,10"

DATA in tblMetric is this.

DATA
ID VALUE
1 xxxxX
2 xxxxY
3 xxxxZ
4 xxxxA
5 xxxxB
6 xxxxC
7 xxxxD
8 xxxxE
...

SELECT *
FROM tblMetric
ORDER BY ....here is where I will split the @orderBY to get this:
RESULT:

1 xxxxx
3 xxxxx
8 xxxxx
10 xxxxx
2 xxxxx
4 xxxxx
5 xxxxxx
6 xxxxx
7 xxxxx
9 xxxxxx

Looking for the most elegant way.

tnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 20:19:18

you can do like this


SELECT t.ID,t.Value
FROM YourTable t
INNER JOIN dbo.ParseValues(@String,',')f
ON f.Val = t.ID
ORDER BY f.ID

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-28 : 07:13:19

The method posted by visakh16 will work, but if you wanted a more set based approach and don't want to create a UDF you can use the following, which I just came up with:


DECLARE @OrderBy varchar(max) = '2,6,1,7,10,400,8,';

WITH Numbers(num)
AS
(
	SELECT 1
	UNION ALL
	SELECT
	  num+1
	FROM
	  Numbers
	WHERE
	  num < LEN(@OrderBy)
),
OrderBy(num, value, ranking)
AS
(
	SELECT
		num, 
		SUBSTRING(@OrderBy, num, CHARINDEX(',', @OrderBy, num)-num) as value, 
		ROW_NUMBER() OVER (PARTITION BY CHARINDEX(',', @OrderBy, num) ORDER BY CHARINDEX(',', @OrderBy, num)-num DESC) as ranking
	FROM
		Numbers 
	WHERE
		num < LEN(@OrderBy)
		AND num != CHARINDEX(',', @OrderBy, num)
)
SELECT
	t.*
FROM
	test_table t
		INNER JOIN OrderBy o ON t.id = o.value AND ranking = 1
ORDER BY
	o.num

This assumes that each number in the list is an id key in the table you are joining to.

Notice that I have a comma at the end of the string. If you are getting passed a string from somewhere you will need to append a comma to the end of the string for this method to get the last value.

Most parsing routines you will see use loops, which isn't a huge deal on short strings, but this set based approach should do better if the string is large.

----------------------------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-28 : 07:58:54

quote:
Originally posted by visakh16

you can do like this

SELECT t.ID,t.Value
FROM YourTable t
INNER JOIN dbo.ParseValues(@String,',')f
ON f.Val = t.ID
ORDER BY f.ID

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think this will not give the wanted output as the OP posted above because of the inner join will suppress the other values.

So maybe with this adjustment?


SELECT t.ID,t.Value
FROM YourTable t
left JOIN dbo.ParseValues(@String,',')f
ON f.Val = t.ID
ORDER BY coalesce(f.ID,t.ID)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 15:41:59

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

you can do like this

SELECT t.ID,t.Value
FROM YourTable t
INNER JOIN dbo.ParseValues(@String,',')f
ON f.Val = t.ID
ORDER BY f.ID

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think this will not give the wanted output as the OP posted above because of the inner join will suppress the other values.

So maybe with this adjustment?

SELECT t.ID,t.Value
FROM YourTable t
left JOIN dbo.ParseValues(@String,',')f
ON f.Val = t.ID
ORDER BY coalesce(f.ID,t.ID)

No, you're never too old to Yak'n'Roll if you're too young to die.

yep.. makes sense

thanks for the catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources