Avatar of splanton
splanton
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Troublesome retrieve to find a list of records and the records immediatly before...

OK, here is the problem. I have to 'fix' some data that has got corrupted (a bug in some software has led to data corruption - it has gone unnoticed for some time and has left a right mess). Anyway...

I have order records that are made up of two parts:

An order pending
An order actioned

A simplified version of the order record would look like this:

ID [int]
JobRef [varchar](10)
Status [varchar](10)

Open in new window


The real record has many other fields but they are immaterial for the purposes of this question.

Data might look like this:

ID	JobRef	Status
1001	n/a	Pending
1002	5001234	Actioned
1003	n/a	Pending
1004	1000005	Actioned
1005	n/a	Pending
1006	5001236	Actioned
1007	n/a	Pending
1008	5001236	Actioned
...

Open in new window


I need to find all the records with a JobRef LIKE '500%' AND I also need to find the record with an ID that immediatly preceeds it. Otherwise I have no way of finding all those 'Pending' records that belong to the corresponding Actioned records!

If you have a clever answer to this I'm all ears because I'm fresh out of ideas.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
splanton

8/22/2022 - Mon
deighton

SELECT * FROM TABLE WHERE JobRef LIKE '500%'
UNION ALL
SELECT * FROM TABLE WHERE ID IN (SELECT ID-1 FROM TABLE T2 WHERE T2.JobRef LIKE '500%')


that would work if ID's are sequential wouldn't it?  but if you have IDs going say 1000,1001,1003
then that would be more tricky (but still possible with rownumber I Think)
ASKER CERTIFIED SOLUTION
deighton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
splanton

ASKER
Brilliant Solution - well done! Simple when you see it :)

The points and my thanks are yours.

I have a follow on question: how would I convert this into a delete statement?
splanton

ASKER
Ok , got it :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy