How to order “intercalary” rows

[mfaqueiroz]

Hi!
I have 1 table with State "On" and "Off" that belong to a certain machine, I want to order Off On Off On Off On considering that the "Off" following by the "One" are in the same machine.


Input:
State Machine
On A
On F
Off H
On D
On H
On A
Off B
Off B
Off F
Off K
On F
Off D
On B
Off A
....


Output:
State Machine
Off A
On A
Off B
On B
....




I really appreciate your help,

 

[plog]

Huh? I didn't understand your explanation, but from the example data you posted it looks like you want to order by Machine then by State:

SELECT State, Machine FROM YourTableNameHere
ORDER BY Machine, State

 

[mfaqueiroz]

Sorry, I will try to explain again.
So,
1.I have a table with status, Machines and Data/Hour.
2.The table have a lot of values and sometimes I have one On/Off that don't match with other Off/On.
3. I want to order by pairs Off-One, this pairs have to belong to the same machine.

So I will have my table order by Data/Hour, every Off will be follow by one One, that belong to the same machine.

I hope I have be more clear

 

[plog]

No, made it less clear actually. What does Data/Hour have to do with this? Also, how do you know what records are pairs?

Can you post better sample data to demonstrate your issue?

 

[The_Doc_Man]

First, use an "ORDER BY MACHINE, EVENTTIME" to get things in the right order.

Second, if you are trying to generate a "figure of merit" for a machine to show how long it was in service, your ability to do this depends on the quality and origins of the machine state log. If there is ever anything that prevents you from getting an "Off" log entry then your statement of your perceived conditions is incorrect. I.e. it would be possible to have two "ON" events in a row with no intervening "OFF" event.

If you are depending on the machine to tell you it is going down, you might NEVER get 100% reliable data. If you are depending on someone's manual entry that data, then you have at least some hope of a properly contiguous dataset. That would be a matter of your shop's operational procedures, not your machine's logging abilities.