Link to home
Start Free TrialLog in
Avatar of splanton
splantonFlag 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.
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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?
Ok , got it :)