Wednesday, May 22, 2013

SQL - display selective output that is not sequenced last in a multiple occurrence.

I have a table like below

OP    OP_var    SPS    SPS_sq 
1010    01       KEB_x    01 
1010    01       KEK_x    02 
1010    02       KEH_c    01 
1010    02       KEK_y    02 
1010    02       KEB_d    03 
1020    01       KEK_f    01 
1020    01       KEE_g    02

The OP column has variance (OP_var) and within it is a group of SPS. SPS_sq is the sequencing of these SPS lines within the OP+OP_var.

I would like to display KEK% where the KEK%'s SPS_sq is not last (meaning, the KEK% is either first or anywhere in the middle of the sequence number of the OP and OP_var as long as it is not last.

The output should look like this :

OP    OP_var    SPS      SPS_sq
1010    02        KEK_y    02
1020    01        KEK_f     01    

ignore all KEK% that is SPS_sq last within the OP+OP_var.

Thank you for your help. Really appreciate it.