Numbering Results

[sharmstr]

I have two tables. One with orders and one with lines items for the orders. The line items have a unique code that relates to the line item number. When sorting on this unique code, you are able to see the order in which the item was added to the order. I'd like a query to return an actual line number instead of this unique code. Here's the layout

Order Table (orders):
orderNumber

Line Item Table (orderLines):
orderNumber
lineItem
partNumber

Example Query:
select orderNumber, lineItem, partNumber
FROM orders INNER JOIN
orderLines ON orders.csono = orderLines.csono

Example Data:
orderNumber, lineItem, partNumber
12345, _1LK0LSRTJ, part123
12345, _1LK0LSXU7, part456
12345, _1LK0LT39X, part789

I would like the results to look like this
12345-1, part123
12345-2, part456
12345-3, part789

Thank you for any help.

[BitBlt]

I assumed that you wanted each new order number to start with line number 1 like so:
12345-1
12345-2
12345-3
23456-1
23456-2
etc.

After playing a little fast and loose with your sample query and test data, here's what I came up with:

select 
cast(ord.orderNumber as varchar(5)) + '-' + cast(ROW_NUMBER() over (PARTITION BY ord.orderNumber order by partnumber asc) as varchar(10)) as OrderLineNumber
, partNumber
FROM dbo.orders ord
INNER JOIN dbo.orderLines ordln
ON ord.orderNumber = ordln.orderNumber

The important part here is the whole PARTITION BY clause. You might want to look in the help file if you desire more details.

Here is the scenario I built for testing on SQL2008:

create table dbo.orders
(orderNumber int)

create table dbo.orderLines
(
orderNumber int,
lineItem varchar(10),
partNumber varchar(10)
)

insert into dbo.orders
(orderNumber) values
(12345), (23456), (34567), (45678)

insert into dbo.orderLines
(orderNumber, lineItem, partNumber)
values
(12345, 'A1LK0LSRTJ', 'parta123'),
(12345, 'A1LK0LSXU7', 'parta456'),
(12345, 'A1LK0LT39X', 'parta789'),
(23456, 'B1LK0LSRTJ', 'partb123'),
(23456, 'B1LK0LSXU7', 'partb456'),
(23456, 'B1LK0LT39X', 'partb789'),
(34567, 'C1LK0LSRTJ', 'partc123'),
(34567, 'C1LK0LSXU7', 'partc456'),
(34567, 'C1LK0LT39X', 'partc789'),
(45678, 'D1LK0LSRTJ', 'partd123'),
(45678, 'D1LK0LSXU7', 'partd456'),
(45678, 'D1LK0LT39X', 'partd789')

And, finally, here are my results:

OrderLineNumber	partNumber
12345-1	         parta123
12345-2	         parta456
12345-3	         parta789
23456-1	         partb123
23456-2	         partb456
23456-3	         partb789
34567-1	         partc123
34567-2	         partc456
34567-3	         partc789
45678-1	         partd123
45678-2	         partd456
45678-3	         partd789

Hope this helps! Good luck!

[sharmstr]

This looks great, though doesnt work with SQL2000. I know, I know, I should have mentioned that. I'm really sorry for wasting you time and do appreciate the response. I'm looking at using IDENTITY instead of ROW_NUMBER now.

[BitBlt]

Aw, dang! Oh, well...it was fun to try.

I do have a cursor-based solution for SQL2000, though. It's not pretty, but it works.

create table #temp
(orderNumber int,
orderLineItemNumber int,
lineItem varchar(10),
partNumber varchar(10)
)

declare @ordNbr int
declare @ordFetchStatus int
declare @ordLnNbr int
declare @ordLnFetchStatus int
declare @lineItem varchar(10)
declare @partNumber varchar(10)

declare ordCursor cursor for select ordernumber from dbo.orders
open ordCursor
fetch next from ordCursor into @ordNbr
select @ordFetchStatus = @@fetch_status
while @ordFetchStatus = 0
begin
    set @ordLnNbr = 0
    declare ordLnCursor cursor for select lineItem, partNumber from dbo.orderLines where ordernumber = @ordNbr order by lineitem
    open ordLnCursor
    fetch next from ordLnCursor into @lineItem, @partNumber
    select @ordLnFetchStatus = @@fetch_status
    while @ordLnFetchStatus = 0
    begin
        set @ordLnNbr = @ordLnNbr + 1
        insert into #temp (orderNumber, orderLineItemNumber, lineItem, partNumber)
        values (@ordNbr, @ordLnNbr, @lineItem, @partNumber)
        fetch next from ordLnCursor into @lineItem, @partNumber
        select @ordLnFetchStatus = @@fetch_status
    end
    close ordLnCursor
    deallocate ordLnCursor
    fetch next from ordCursor into @ordNbr
    select @ordFetchStatus = @@fetch_status
end
close ordCursor
deallocate ordCursor

select cast(orderNumber as varchar(5)) + '-' + cast(orderLineItemNumber as varchar(5)) as OrderLineNumber, partNumber from #temp 
drop table #temp

Tested with the same table structure and test data, seems to work fine.

[sharmstr]

WOW. That is ugly. But it works! No way I could have figured that out. Thanks a million!

[BitBlt]

You're welcome, happy to help. Be sure to mark the thread "Solved".