mysql - How to compare 2 row in same table -

please help, table

   date     column1    column2          trx 2015-07-01  **side1    internet**     777903315 2015-07-01  **side1    internet**      41426210 2015-07-01  side1      unlimited        2263500 2015-07-01  side1      business          427000  2015-07-01  side1      extreme          3540900 2015-07-01  side1      lifestyle       59360000 2015-07-01  side1      socialita      240850500 2015-07-01  **side2    unlimited**         6160 2015-07-01  **side2    unlimited**    113502000 

and want select table result :

   date     column1    column2          trx      type 2015-07-01  **side1    internet**     777903315  pre 2015-07-01  **side1    internet**      41426210  post 2015-07-01  side1      unlimited        2263500  pre 2015-07-01  side1      business          427000  pre 2015-07-01  side1      extreme          3540900  pre 2015-07-01  side1      lifestyle       59360000  pre 2015-07-01  side1      socialita      240850500  pre 2015-07-01  **side2    unlimited**         6160  post 2015-07-01  **side2    unlimited**    113502000  pre 

row have same value in column1 , column2 row after minimum trx add field type 'post' , maximun 'pre'

you can use following query:

select m.*,               case trx            when mintrx 'post'           when maxtrx 'pre'           else 'pre'        end type mytable m left join (    select column1, column2, min(trx) mintrx, max(trx) maxtrx    mytable    group column1, column2    having min(trx) <> max(trx) ) t on m.column1 = t.column1 , m.column2 = t.column2 

this query performs left join derived table containing duplicate column1, column2 rows. minimum / maximum trx matches produce post / pre values respectively, whereas pre default value non-matched table rows.

demo here