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

sequence

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-22 : 18:29:10

I have a table with 4 columns
col1= batchid, col2= action, col3 = category, col4 = sequence.

now col1 can have values like "batch01" , "batch02" and so on.. upto n.
col2 can have either 'fail' or 'pass'
col3 can have number 1,2,3 only
col4 needs to be updated.

right now data is mixed (not in sequence)

SEQUENCE LOGIC: 'batch01' goes first, then in col2 'pass' then arrange it by increasing order of category and assign the sequence.

sample result:

batchid action category sequence

batch01 pass 2 1
batch01 pass 3 2
batch01 fail 1 3
batch01 fail 2 4
batch01 fail 3 5
batch02 pass 1 6
batch02 fail 3 7
..................
and so on..

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 18:34:38

If you question is the SELECT statement? then something like this:

SELECT batchid, action, category, sequence
FROM MyTable
ORDER BY col1, CASE WHEN col2 = 'pass' THEN 1 ELSE 2 END, col3, col4

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-22 : 18:41:51

I have to assign the sequence according to the logic.
syntax should be like

update my_table
set seq = newseq
from ( select row_number () over(order by batchid),
seq,
newseq = case action
when 'pass' then + row_number() over(.......

so it should look for batchid first (increasing order)
then case action and then category...

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 19:39:34

I normally do that by:


SELECT [MyID] = IDENTITY(int, 1, 1),
       PKCol1, PKCol2, ...
INTO #MyTemp
FROM MyTable
ORDER BY col1, CASE WHEN col2 = 'pass' THEN 1 ELSE 2 END, col3, col4

UPDATE U
SET MySequenceColumn = T.MyID
FROM MyTable AS U
     JOIN #MyTemp AS T
       ON T.PKCol1 = U.PKCol1
      AND T.PKCol2 = U.PKCol2
      ....

but using ROW_NUMBER() OVER would probably work fine too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources