getting sequence number in 2000

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

getting sequence number in 2000

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-04-18 : 11:45:32

I have a table like this

create table
#invoice (invoice_no varchar(5), invoice_amount int)

INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700

Now I want a output like this

INVOICE_NO AMOUNT SEQUENCE_NO
A 100 1
A 200 2
A 300 3
B 400 1
B 500 2
B 600 3
B 700 4

The sequence_no should set back to 1 on change of invoice_no
these are items in a an invoice. they just want a sequence_no

I can do this using cursors, but if anyone can help with WHILE LOOP
would be great

THANKS

-----------------------------------------------------------------------------------------------
Ashley Rhodes

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 12:28:56

Not a while loop but

select invoice_no, invoice_amount,
sequence_no = rank() over (partition by invoice_no order by invoice_amount)
from #invoice

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-04-18 : 12:33:37

I don't have 2005 its 2000

-----------------------------------------------------------------------------------------------
Ashley Rhodes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 15:05:50

[code]SELECT (SELECT COUNT(*) + 1 FROM YourTable WHERE invoice_no=t.invoice_no AND invoice_amount < t.invoice_amount) AS Seq,
invoice_no, invoice_amount
FROM YourTable t[/code]

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-04-18 : 15:16:01

YOUR QUERY FAILS WHEN THE DATA IS LIKE THIS
THE DIFFERENT ITEMS IN INVOICE ARE VERY LIKELY TO HAVE SAME AMOUNT

INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 100 UNION
SELECT 'A', 100 UNION
SELECT 'B', 100 UNION
SELECT 'B', 100 UNION
SELECT 'B', 100 UNION
SELECT 'B', 100

-----------------------------------------------------------------------------------------------
Ashley Rhodes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 15:27:56

try this then:-

create table 
#invoice (id int identity(1,1),invoice_no varchar(5), invoice_amount int)

INSERT INTO #invoice (invoice_no,invoice_amount)
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700


SELECT ID - (SELECT COUNT(*) 
             FROM YourTable 
             WHERE invoice_no<>t.invoice_no 
             AND ID<t.ID) AS Seq,
invoice_no, invoice_amount
FROM YourTable t

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 15:35:04

sorry

select *, identity(int,1,1) as id
into #a from #invoice

SELECT (SELECT COUNT(*) + 1 FROM #a t2 WHERE invoice_no=t.invoice_no AND (t2.invoice_amount < t.invoice_amount or (t2.invoice_amount = t.invoice_amount and t2.id < t.id))) AS Seq,
invoice_no, invoice_amount
FROM #a t
order by Invoice_no, Seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-04-18 : 16:58:42

NR your last post does not work as well
just try to execute and see
it return only 2 rows

-----------------------------------------------------------------------------------------------
Ashley Rhodes

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 20:35:15

That'll be because there are only two rows in #invoice.
Try changing union to union all.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-19 : 00:39:54

Hi,

Try with this

create table
#invoice (invoice_no varchar(5), invoice_amount int, Seq Int)

INSERT INTO #invoice
SELECT 'A', 100, 0 UNION
SELECT 'A', 200, 0 UNION
SELECT 'A', 300, 0 UNION
SELECT 'B', 400, 0 UNION
SELECT 'B', 500, 0 UNION
SELECT 'B', 600, 0 UNION
SELECT 'B', 700, 0
--SELECT * FROM @T

UPDATE T1
SET SEQ = (SELECT COUNT(*) FROM #invoice T WHERE T.invoice_no = T1.invoice_no AND T.invoice_amount <= T1.invoice_amount )
FROM #invoice T1
SELECT * fROM #invoice

Drop Table #invoice

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-19 : 02:53:05

If you want to show the dagta in Reports, you can easily make use RecordNumber and reset in each group

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources