Numbering the data - 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

Numbering the data

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-29 : 16:21:22

I have a table with 5 columns.
col1 = ID (int)
col2 = fruits (8 values)
col3 = Batch (01, 02, 03, .... N )
col4 = can have 5 values (say Xvalue) (111,222,333,444,555)
col5 = sequence(int).

Logic: Fruits are arranged in order.
Apple>Banana>Mango>pineapple>Guava>Orange etc... (8 total)

Every fruit can have different batch numbers (01,05,45, etc..)
Now every batch can have 5 possible xValues.

I want to assign the sequence for the data like:
Data:
1 Banana 02 555
2 Apple 01 333
3 Apple 01 111
4 Apple 02 555
5 Banana 01 555

ID 3 should be 1st in seq
ID 2 should be second
and so on.....

Result:

col1(id) col2 col3(batch) col4(xvalue) col5(seq)
3 Apple 01 111 01
2 Apple 01 333 02
4 Apple 02 555 03
5 Banana 01 555 04
1 Banana 02 555 05

Again It should sort according to fruit Name,
then it should look for batch and then xValue and then assign the sequence.

Any help would be appreciated.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-29 : 16:25:21

you mean this?


select col1
       ,col2
       ,col3
       ,col4
       ,col5 = row_number() over (order by col2,col3,col4)
from   yourTable

EDIT:
Is this the same question in a different post?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163633

Be One with the Optimizer
TG

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-29 : 16:29:55

Apple, Banana, NY, LA.. this looks like a HW problem :)

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-29 : 16:31:42

@ TG:

Sorry Lets Change col4 values to ( B101, Q105, S096, J012 and L123 )

I want the sequence for col4 as (Q105 > B101 > J012 > L123 > S096)
now what technique should I use ?
I know CASE, But where ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-29 : 16:36:48

Don't cross post - use this thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163633

Be One with the Optimizer
TG

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-29 : 16:39:29

no, That post Addresses a different scenario.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-29 : 16:47:54

Ok then - to avoid that ongoing back and forth like the other thread please post DDL/DML (code to create and populate a temp table) for enough sample data to illustrate your problem. Then include the desired output based on your sample data.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources