Find Last Record In Sequence

Adam Jasper

I have a table in Sql Server structured something like this:

RowId OrderId OldOrderId Type Example Data
8787 1 NULL I The first order in sequence 1
9837 2 1 U The second order in sequence 1
10838 3 2 U The third order in sequence 1
11873 4 3 U The fourth order in sequence 1
12554 5 NULL I The first order in sequence 2
14113 6 5 U The second order in sequence 2
15003 7 6 U The third order in sequence 2
18343 8 7 U The fourth order in sequence 2

What I am trying to do is find the last record in each sequence using only the columns I have in the table. I cannot add anything to group by as I am not in control of the data. Has anyone got any ideas? I have done something like this before but I cannot seem to put my hands on it at the moment. Thanks in advance

Simon_Whale

Does the solution have to be SQL Based?

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Adam Jasper

Yes, I need it to be SQL based. I think I'm nearly there, think possibly being the operative word :)

Blue_Boy

I hope this will help you

select max(mt.rowid) as RowId,

(select top 1 mt1.Data from mytable mt1 where rowid = max(mt.rowid)) as Data
from mytable mt
group by substring(mt.data, charindex('order',mt.data),len(mt.data))
order by max(mt.rowid) desc

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post. www.cacttus.com

Adam Jasper

Thanks for the reply, unfortunately there is nothing to group by. I need to find the last in sequence using only the OrderId and OldOrderId values.

Blue_Boy

Based on that data of table which you have gave in your first post then query will work. Otherwise post data structure how they are in table. by this query

select max(mt.OldOrderID) as OldOrderID,

(select top 1 mt1.Data from mytable mt1 where mt1.OldOrderID = max(mt.OldOrderID)) as Data
from mytable mt
group by substring(mt.data, charindex('order',mt.data),len(mt.data))
order by max(mt.OldOrderID) desc

you will get this result OldOrderID Example Data 7 The fourth order in sequence 2 3 The fourth order in sequence 1

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post. www.cacttus.com

Adam Jasper

Sorry, the data in ExampleData column could be anything, just purely an example to indicate that the record has additional data.

Blue_Boy

Ok, how about to explain you more your case, which result you want to get from your table data?

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post. www.cacttus.com

Adam Jasper

The resultset you had in your original post is the resultset I am looking for, however, like I said in my original post, there is nothing to group by. What I probably should have posted is given the following dataset, how do I find the last RowID in the sequence and missed off the ExampleData column.

Adam Jasper

For anyone who is interested, here is my solution:

SELECT
Orders.ExampleData
FROM
OrderSeq AS Orders
LEFT JOIN
OrderSeq AS Updates ON (Orders.OrderId = Updates.OldOrderId) AND (Updates.[Type] = 'U')
where
(Updates.RowId IS NULL)

I knew it was easy, just couldn't get my head around it this morning. Thanks to everyone who replied.