Wsrtjtyk

I want to get the values of

I have a table named slot_machine_history, that represents all the movements (sold, new relocation, destroyed,suspended) of a given slot machine.

I want to obtain the row before each sold movement identified by 'V' and the 'V' movement. Becuase I want to obtain the company that it was the owner of the slot, and the new owner of the slot

  slot_machine_code        movement_history       current_company

  612134                    'NEW'                   1

  612134                    'TRANSPORT'             1

  612134                    'TRANSPORT'             1

  612134                    'V'                     10  

  612134                    'TRANSPORT'             10

  612134                    'SUSPENDED'             10

 612134                 'V'                     14

my sql query should return:

 612134                 'TRANSPORT'             1

 612134                 'V'                     10

 612134                 'SUSPENDED'             10

 612134                 'V'                     14

You need some sensible way to order the rows for this. Hopefully your table has something that increments like a date or counter for every movement event that a machine undertakes:

  movement_event slot_machine_code        movement_history       current_company

  1              612134                    'NEW'                   1

  2              612134                    'TRANSPORT'             1

  3              612134                    'TRANSPORT'             1

  4              612134                    'V'                     10  

  5              612134                    'TRANSPORT'             10

  6              612134                    'SUSPENDED'             10

  7              612134                    'V'                     14

You can then do a query like:

SELECT slot_machine_code, movement_history, current_company FROM

(

  SELECT t.*, LEAD(movement_history) OVER(PARTITION BY slot_machine_code ORDER BY movement_event ASC) as next_Movement_history

  FROM your_table t

) z

WHERE

  z.movement_history = 'V' OR z.next_movement_history = 'V'

If there is no column that denotes the order that records were written, you're kinda up the creek. Add one.

If your current table just-so happens to return the rows in the order they were inserted then it will be enough to use that to add a rownum or similar, but do it soon, because the order that a database returns rows in is never guaranteed to be the order of insertion and internally data reorganizations will change the order of rows returned by an unordered (having no ORDER BY clause) query