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.
sequence should be first according to col1
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.
NOTE: 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