Counting unique entries in a SQL Statement

I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.

I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".

Does this make sense?

EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....

SAGMIR 025 001 HEM/FIR HLF550...

SAGMIR 025 001 HEM/FIR KILN-D HLF505...

SAGMIR 025 002 HEM/FIR HLF660....

The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...

Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!

Michael

Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :

create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)

insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID

select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID

drop table #tempinv

I think this is what you want....