Get next record 1

[cerebalbore]

Hi all,

I need some help getting the next record in sequence - I have already tried numerous things to get this done and haven't found an answer that will do what I want. The only thing I have found out is that what I want to do isn't 'good practice'.

So here's a snippet of my table...

Order_no Op_no Op_desc
10001 0025 Kitting
10001 0050 Auditing
10001 0100 Release kit
10002 0050 Auditing
10002 0100 Release kit
10002 0150 Validate serial nos
10003 0700 Assembly
10003 0800 Inspect
10003 0900 Receive finished goods

And here's what I want as a result set...

Order_no Op_no Op_desc Next_op
10001 0025 Kitting 0050
10001 0050 Auditing 0100
10001 0100 Release kit
10002 0050 Auditing 0100
10002 0100 Release kit 0150
10002 0150 Validate serial nos
10003 0700 Assembly 0800
10003 0800 Inspect 0900
10003 0900 Receive finished goods

Basically, all the time the order number is the same as the one below it, I want the op number (and other details later) of the next record. The data needs to be sorted by order number then op number.

Some of the operations will not have a next operation as it will be the last one in the sequence for that order.

It's so I can identify what the next stage in the operation sequence an order is due to go to (it will be combined with another couple of tables later). It's to do with what work is at what stage.

Any help is appreciated - I'm ok with the basics of SQL, like nested queries and ctes, but that's as far as it goes!

Thanks

Kat


I'm such a noob

 

[Rhys666]

Have you tried using a CTE to get the partitioed/ordered row numbers and a self join. I can't vouch for the efficiency of scale as I don;t know the volume of data you're working with but I.E.,

DECLARE @Temp	TABLE
(
	OrderNo			INT
	,OperationNo	CHAR(5)
	,OperationDesc	VARCHAR(32)
);
INSERT INTO @Temp VALUES (10001, '0025', 'Kitting');
INSERT INTO @Temp VALUES (10001, '0050', 'Auditing');
INSERT INTO @Temp VALUES (10001, '0100', 'Release kit');
INSERT INTO @Temp VALUES (10002, '0050', 'Auditing');
INSERT INTO @Temp VALUES (10002, '0100', 'Release kit');
INSERT INTO @Temp VALUES (10002, '0150', 'Validate serial nos');
INSERT INTO @Temp VALUES (10003, '0700', 'Assembly');
INSERT INTO @Temp VALUES (10003, '0800', 'Inspect');
INSERT INTO @Temp VALUES (10003, '0900', 'Receive finished goods');

WITH Temp1_CTE
AS
(
	SELECT
		*
		,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY OperationNo) AS 'RowNo'
	FROM
		@Temp t1
)
SELECT
	t1.*
	,t2.OperationNo AS 'NextOperationNo'
FROM
	Temp1_CTE t1
LEFT OUTER JOIN
	Temp1_CTE t2 ON t1.OrderNo = t2.OrderNo AND t2.RowNo = t1.rowNo+1;

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[cerebalbore]

Thank you very much, this does exactly what I need - I hadn't thought to use row numbers with a cte for this. Nifty little trick that.

It seems to be pretty quick too, 15 seconds for 200k rows. I can now have a play.

I'm such a noob

 

[Rhys666]

As I said, I'm not sure about the efficiency of scale but it may be worth playing with a second CTE to derive the next row number to join on in the self join set rather than directly in the join;

DECLARE @Temp    TABLE
(
    OrderNo            INT
    ,OperationNo    CHAR(5)
    ,OperationDesc    VARCHAR(32)
);
INSERT INTO @Temp VALUES (10001, '0025', 'Kitting');
INSERT INTO @Temp VALUES (10001, '0050', 'Auditing');
INSERT INTO @Temp VALUES (10001, '0100', 'Release kit');
INSERT INTO @Temp VALUES (10002, '0050', 'Auditing');
INSERT INTO @Temp VALUES (10002, '0100', 'Release kit');
INSERT INTO @Temp VALUES (10002, '0150', 'Validate serial nos');
INSERT INTO @Temp VALUES (10003, '0700', 'Assembly');
INSERT INTO @Temp VALUES (10003, '0800', 'Inspect');
INSERT INTO @Temp VALUES (10003, '0900', 'Receive finished goods');

WITH Temp1_CTE
AS
(
    SELECT
        *
        ,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY OperationNo) AS 'RowNo'
    FROM
        @Temp t1
)
,Temp2_CTE
AS
(
    SELECT
        t1.*
        ,t1.RowNo+1 AS 'NextRowNo'
    FROM
        Temp1_CTE t1
)
SELECT
    t1.OrderNo
	,t1.OperationNo
	,t1.OperationDesc
    ,t2.OperationNo AS 'NextOperationNo'
FROM
    Temp2_CTE t1
LEFT OUTER JOIN
    Temp2_CTE t2 ON t1.OrderNo = t2.OrderNo AND t1.NextRowNo = t2.RowNo;

I think its almost certainly worth playing with a little more to get it running in as good a time as possible at this bottom level if you're doing further processing.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett