How to sort the selected values from a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to sort the selected values from a table?

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-13 : 10:26:15

If I make a ‘Select’ to a table selecting only a few specified items, Is it possible to retrieve the items in a specified order? I mean, an order which is not specified in the table. For example, in this table:

Table name: Supermarket_products
Product_id / Product_name / Family_num / Sort_Order
1/Onions/1 / 1
2/Carrots/1 / 1
3/Potatoes/1 / 1
4/Other Vegetables/1 / 99
5/Chicken/2 / 1
6/Beef/2 / 1
7/Other Meats/2 / 99
8/Spinach/1 / 1
9/Peas/1 / 1
10/Pork/2 / 1
11/Duck/2 / 1

Normally I make the order thus: ORDER BY Family_num, Sort_Order, Product_name

But now, I want to select only: Potatoes, Carrots and Onions. And I would like to retrieve them in the same order. I don’ t want to change the ‘Sort _Order’ field because for almost all the queries I make, this order is correct. So, I only want to retrieve that three items in that order in this case.

The Stored Procedure would be something like this:


USE database_GG1
GO
CREATE PROCEDURE sel_Potatoes_Carrots_Onions
As
SELECT Product_id, Product_name From Supermarket_products 
 WHERE Product_id = 1 or Product_id = 2 or Product_id = 3 
ORDER (by product_id (3,2,1))
GO

Somebody can help me to correct my SP or could advise me how I have to do it to obtain the same results?

Thank you,
Cesar

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-13 : 10:31:49

never tried this before...this might work:


Declare @productList varchar(100)
Set @productList = '003,002,001'

SELECT 
	Product_id, 
	Product_name 
From Supermarket_products 
WHERE ','+@productList+',' like '%,'+right('000' + product_Id,3)+',%'
ORDER by charindex(right('000' + product_Id,3),'003,002,001')

--And this should swap the places of 1 & 2

Set @productList = '003,001,002'

SELECT 
	Product_id, 
	Product_name 
From Supermarket_products 
WHERE ','+@productList+',' like '%,'+right('000' + product_Id,3)+',%'
ORDER by charindex(right('000' + product_Id,3),'003,002,001')

Corey

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-13 : 10:54:06

I am not still prepared to understand this code

. Is not there another easiest way to do it?
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-13 : 11:23:41

its pretty simple really....

the where clause:
WHERE ','+@productList+',' like '%,'+right('000' + product_Id,3)+',%'

This allows you to set a list in a variable and then use that variable to filter your records. basically the code translates into:

--when product_Id = 1
where ',003,002,001,' like '%,001,%' --(true)

--when product_Id = 4
where ',003,002,001,' like '%,004,%' --(false)

The order By:
ORDER by charindex(right('000' + product_Id,3),'003,002,001')

This looks for the first instance of the product_Id in the variable and returns the character number the product_Id starts on. Using that as the order by, they should come out in the order they are listed in the variable.

Did you give it a try? It should work.

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-13 : 11:54:58

but as he asked for something simple

could be easier to understand...


declare @in varchar(50)
set @in = '3,1,2'

SELECT 	t1.*
From 	@Supermarket_products t1
	inner join dbo.Split(@in, ',') t2 on t1.Product_id = t2.Data
order by t2.Id

dbo.Split is also courtesy of Corey (great UDF by the way):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39686

corey, why does this ORDER by charindex(right('000' + product_Id,3),'003,002,001')
look so familiar?? was it here somewhere?

Go with the flow & have fun! Else fight the flow

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-13 : 12:17:42

Thank you very much for your aid. I think that I have found another easiest way:
[CODE]
USE database_GG1
GO
CREATE PROCEDURE sel_Potatoes_Carrots_Onions
As
SELECT Product_id, Product_name From Supermarket_products
WHERE Product_name IN ('Potatoes', 'Carrots', 'Onions')

ORDER BY
CASE Product_name
WHEN 'Potatoes' THEN 1
WHEN 'Carrots' THEN 2
WHEN 'Onions' THEN 3
END
GO
[/CODE]

Or perhaps is it better thus?:
[CODE]
USE database_GG1
GO
CREATE PROCEDURE sel_Potatoes_Carrots_Onions
As
SELECT Product_id, Product_name From Supermarket_products
WHERE Product_id IN ('3', '2', '1')

ORDER BY
CASE Product_id
WHEN '3' THEN 1
WHEN '2' THEN 2
WHEN '1' THEN 3
END
GO
[/CODE]

What do you think? Which system is better?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-13 : 12:22:26

well the reason i don't like this way is that if you select 10 products you need to write 10 WHEN ... THEN statements.
it's not generic. corey's or mine is better, i'd use coreys.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-13 : 14:53:02

quote:

corey, why does this ORDER by charindex(right('000' + product_Id,3),'003,002,001')
look so familiar?? was it here somewhere?

I don't know where that came from... I don't remember seeing it somewhere, it just seemed right. I like it though

Corey

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-14 : 07:59:47

Probably, the most common practice is to do it thus:

Table: Supermarket_products
Product_id / Product_name / Family_num / Sort_Order_A / Sort_Order_B
1/Onions/1 / 1 / 3
2/Carrots/1 / 1 / 2
3/Potatoes/1 / 1 / 1
4/Other Vegetables/1 / 99 / null
5/Chicken/2 / 1 / null
6/Beef/2 / 1 / null
7/Other Meats/2 / 99 / null
8/Spinach/1 / 1 / null
9/Peas/1 / 1 / null
10/Pork/2 / 1 / null
11/Duck/2 / 1 / null

Then, write the SP thus:


USE database_GG1
GO
CREATE PROCEDURE sel_Potatoes_Carrots_Onions
As
SELECT Product_id, Product_name From Supermarket_products 

WHERE Product_id IN (3, 2, 1)
ORDER BY Sort_Order_B 
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 08:05:12

that's true :) but we tried to do this without changing the table structure

but how will you effectivly update the sort_order_B to meet your demands?

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 09:14:44

it depends if "Sort B" is an ad-hoc thing, or if it has some true meaning.

If it is ad-hoc, then do it using SQL or temp tables or something.

If the alternate sort is something that needs to be persistant, and/or has meaning then store it in the table. Better yet, find out WHY it should be sorted that way -- it is based on calorie count or color or location in the supermarket, etc? -- and if so, store that attribute in with your data and sort based on the characteristics of your data, instead of "SortCodes" which don't really have much meaning.

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-14 : 09:53:24

Ok, thank you!

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-14 : 13:11:28

I’ m sorry Spirit1, I didn’ t see your last post.

I don’ t know if I have understood your question, but I don’ t need to update the ‘sort_order_B’ column because that sort order is to populate a ‘Select’ web form object, and this sort order will be always the same (I hope). But if some day I need to change the sort order of that column, I will simply open the table and I will type the new sort order numbers, erase them or change them.

It was that your question?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 13:29:56

aha ok. it was

i thought that you will be changing the sort order numerous times.
if it works for you great!

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources