Unanswered: Combining statements with different ranges
I'm trying to figure out a way to speed up a process. This process is trying to find the oldest record for a part number at various workstations in our system. The relevant fields in our table look like this:
This process needs to look first to see if the part exists at a specific workstation or workstations greater than the specified one, in workstation sequence. If still not found, it looks at all workstations, again in workstation sequence.
Say, for example, I was looking for part number ABC. I have workstation numbers 1-10 and the specific workstation I want to start with is workstation 5. Workstation 3 is the only one with the part number at it.
I would do the following lookups:
select workstation, date_entered from inventory where part_number='ABC' and workstation>=5 order by workstation, date_entered;
select workstation, date_entered from inventory where part_number='ABC' order by workstation, date_entered;
If I came across the record for the part in the first lookup, I don't do the second look up.
Is there a way to combine these statements into a single query? I was hoping to figure out how to put it into a single select statement and tell SQL to return workstations 5-10 first followed by 1-4 (in my example). Can something be done with the order by to do this instead of returning straight workstation sequence?
I hope I've made sense.
Thanks very much,
Last edited by ajreynolds; 01-07-10 at 14:02.