Assigning SEQUENCE - 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

General SQL Server Forums

New to SQL Server Programming

Assigning SEQUENCE

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-28 : 15:25:19

Hi again,

now 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:

Banana NY 0001
Banana LA 0002
Banana SF 0003
Blueberry LA 0004
Blueberry SF 0005
Blueberry NY 0006 and so on...

Kindly write a general query for this.
mine is not working accordingly.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 15:38:48

Can you post what you've already tried? Can you also clarify:

quote:
then according to col2 depending on col1
for banana col2 order = NY>LA>SF
for Blueberry, order = LA>SF>NY
and so on...

Because while that may be "business logic", it's not real logic, or at least not readily apparent.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-28 : 15:56:41

Result set will clarify this:
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

now first, sequence by col1 order: Banana > blueberry > Apple > Strawberry

Then sequence by col2, order for banana : NY > LA > SF
for blueberry: LA > SF > NY
for Apple: NY > SF > LA
for Strawberry: NY > LA > SF
please see the resultset again, I hope its crystal clear now. I cant come up with anything. sorry

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 16:08:28

If you only have 12 rows, couldn't you just manually enter the sequence number? The only solution I can think of is about as long as the INSERT statements to generate the data explicitly.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-28 : 16:15:31

no, I have thousands of rows.
Lets put it this way

for banana > NY say I have 100 rows
then banana > LA (50 rows)should start from 101
then banana > SF should start from 151.

sat I want to sort col1>col2 in ascending order of ID.

478 banana NY 0001
479 banana NY 0002
480 banana NY 0003
so on...

Does that makes sense to you ?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-07-28 : 16:47:16

Using case, I think you can solve this problem ...

If you need help in detail, let me know

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-28 : 16:56:58

Namman :

I am using case as follows:

Update my_table
set seq = newseq
from ( select seq,newseq = case col1

when 'Banana' then case = col2

when 'NY' then ......

It is not working at all. moreover I have not defined the order for col1 or col2. Please help. Im stuck.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 17:00:41

select *, ROW_NUMBER() over (order by
case col1
when 'Banana' then case col2
when 'NY' then 1
when 'LA' then 2 else 3 end
when 'Blueberry' then case col2
when 'LA' then 4
when 'SF' then 5 else 6 end
when 'Apple' then case col2
when 'NY' then 7
when 'SF' then 8 else 9 end
when 'Strawberry' then case col2
when 'NY' then 10
when 'LA' then 11 else 12 end
end, id) as Seq
from MyTable

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-28 : 17:07:46

namman:

I have thousands of rows.
Lets put it this way

for banana > NY say I have 100 rows
then banana > LA (50 rows)should start from 101
then banana > SF should start from 151.

sat I want to sort col1>col2 in ascending order of ID.

478 banana NY 0001
479 banana NY 0002
480 banana NY 0003
so on...

Does that makes sense to you ?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-07-29 : 11:38:15

Do you have a solution, ben_53 ?

I think the script provided by robvolk is working fine.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-29 : 15:05:41

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

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-29 : 16:05:35

@Ghanta:

Imagine you have 100000000 rows.
I was looking for automated solution.

Well I found a different route to solve the problem.
I used:

update my_table
set seq = newseq
from (select seq,newseq = case col1
when 'Banana' then + row_number () over(PARTITION by col2 order by col1 )
and so on for all cases
END

It assigns the sequence automatically but my business logic is far beyond this.
Thanks Anyways.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-29 : 16:28:04

I thought you have millions of rows but you only have 4 fruits and 3 locations...

quote:
Originally posted by ben_53

@Ghanta:

Imagine you have 100000000 rows.
I was looking for automated solution.

Well I found a different route to solve the problem.
I used:

update my_table
set seq = newseq
from (select seq,newseq = case col1
when 'Banana' then + row_number () over(PARTITION by col2 order by col1 )
and so on for all cases
END

It assigns the sequence automatically but my business logic is far beyond this.
Thanks Anyways.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-29 : 16:35:43

@ ghanta:

I have a column named ID that I have mentioned earlier. That can actually make million rows with 4 fruits and 3 locations.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-29 : 19:49:14

I guess you didn't try my solution then, it worked fine for the sample data you provided.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources