July 28, 2011 at 2:07 pm
Hi
I want to assign the sequence for a table with 4 columns.
col1 is identity, col4 is named seq.
now, col2 have only 4 values,
Apple,Banana,Strawberry & Blueberry.
col3 can have 3 vales,
LA, SF & NY.
Business logic:
sequence should be first according to col1
Banana>Blueberry>Apple>Strawberry
then according to col2 depending on col1
for banana col2 order = NY>LA>SF
for Blueberry, order = LA>SF>NY
and so on...
So, It should look like:
id col1 col2 seq
102 Banana NY 0001
147 Banana LA 0002
145 Banana SF 0003
234 Blueberry LA 0004
784 Blueberry SF 0005
547 Blueberry NY 0006
874 Apple NY 0007
258 Apple SF 0008
324 Apple LA 0009
154 Strawberry NY 0010
671 Strawberry LA 0011
361 Strawberry SF 0012
Kindly write a general query for this.
mine is not working accordingly.
Thanks
July 28, 2011 at 2:17 pm
To help those who want to help you, please post table definition, sample data and any T-SQL that you have tried to use. For instructions on how to provide the data in an easily consumable manner please click on the first link in my signature block and the article that will be accessed will, in detail explain how do do this.
July 28, 2011 at 7:08 pm
I guess assign number scores to each fruit and place according to your rule and add all the columns into new column or use CTE and use order by that should work.
July 29, 2011 at 12:49 pm
Business logic:
sequence should be first according to col1
Banana>Blueberry>Apple>Strawberry
then according to col2 depending on col1
for banana col2 order = NY>LA>SF
for Blueberry, order = LA>SF>NY
and so on...
How about order by col1, col2?
_______________________________________________________________
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/
July 29, 2011 at 1:06 pm
WITH my_cte
AS (SELECT fruit,
location,
CASE
WHEN fruit = 'Banana' THEN 4
WHEN fruit = 'Blueberry' THEN 3
WHEN fruit = 'Apple' THEN 2
WHEN fruit = 'Strawberry' THEN 1
ELSE 0
END fruitscore,
CASE
WHEN fruit = 'Banana'
AND location = 'NY' THEN 3
WHEN fruit = 'Banana'
AND location = 'LA' THEN 2
WHEN fruit = 'Banana'
AND location = 'SF' THEN 1
WHEN fruit = 'Blueberry'
AND location = 'LA' THEN 3
WHEN fruit = 'Blueberry'
AND location = 'SF' THEN 2
WHEN fruit = 'Blueberry'
AND location = 'NY' THEN 1
WHEN fruit = 'Apple'
AND location = 'SF' THEN 3
WHEN fruit = 'Apple'
AND location = 'NY' THEN 2
WHEN fruit = 'Apple'
AND location = 'LA' THEN 1
WHEN fruit = 'Strawberry'
AND location = 'NY' THEN 3
WHEN fruit = 'Strawberry'
AND location = 'LA' THEN 2
WHEN fruit = 'Strawberry'
AND location = 'SF' THEN 1
ELSE 0
END locscore
FROM tblseq)
SELECT fruit,
location,
fruitscore + locscore AS score,
Row_number() OVER ( ORDER BY fruitscore + locscore DESC) seq
FROM my_cte
ORDER BY score DESC
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply