Sequence no group wise

[T.S.Negi]

Hi All,

I want to give sequence no group wise, as follows:

Section EmpCode
-----------------------
A 1001
A 1003
A 1009
B 1023
B 1030
C 1044
-----------------------

I want to set sequence no. group wise like this for above data.

Seq. Section EmpCode
------------------------------
1 A 1001
2 A 1003
3 A 1009
1 B 1023
2 B 1030
1 C 1044
----------------------------

Suggestion in this regard will be appreciated
Thanks in Advance,

T.S.Negi

[Jens]

How about iterating through the table using a cursor, updating seq with
an iterative value, keeping track of section column.
Whenever the section coloumn changes value the iterator is reset to 1.

DECLARE cur CURSOR FOR SELECT select seq, section FROM your_table
ORDER BY 2 desc
OPEN cur

FETCH cur INTO @seq, @section
SET @iterator = 1
SET @prev = @section

WHILE @@FETCH_STATUS = 0
BEGIN
IF( @section <> @prev)
SET @iterator = 1

UPDATE your_table SET seq = @iterator

SET @iterator= @iterator + 1
SET @prev = @section
FETCH cur INTO @seq, @section
END

[Jens]

A little correction...it should be:

UPDATE your_table SET seq = @iterator WHERE CURRENT OF cur

and NOT:

UPDATE your_table SET seq = @iterator

[Hugo Kornelis]

On 13 Feb 2005 20:53:53 -0800, T.S.Negi wrote:

Hi All,

I want to give sequence no group wise, as follows:

Section EmpCode
-----------------------
A 1001
A 1003
A 1009
B 1023
B 1030
C 1044
-----------------------

I want to set sequence no. group wise like this for above data.

Seq. Section EmpCode
------------------------------
1 A 1001
2 A 1003
3 A 1009
1 B 1023
2 B 1030
1 C 1044
----------------------------

Suggestion in this regard will be appreciated

Hi T.S.,

SELECT COUNT(*) AS Seq,
a.Section,
a.EmpCode
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Section = a.Section
AND b.EmpCode <= a.EmpCode
GROUP BY a.Section,
a.EmpCode

or

SELECT (SELECT COUNT(*)
FROM MyTable AS b
WHERE b.Section = a.Section
AND b.EmpCode <= a.EmpCode) AS Seq,
a.Section,
a.EmpCode
FROM MyTable AS a
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)