Q:

i want distinct refno which is not delivered by the help of sql

   datetime              refno      status      final_status

2016-12-01 10:00:00      123456     hold

2016-12-02 11:10:00      123456     delivered 

2016-12-01 10:00:00      654321     hold

2016-12-02 12:20:00      654321     delivered 

2016-12-01 10:00:00      111111     hold

2016-12-01 01:00:00      222222     hold

i want distinct refno which is not delivered 
I want to output like this:-
output:-
    datetime              refno     status    

2016-12-01 10:00:00      111111     hold       

2016-12-01 01:00:00      222222     hold      

A:

Try this:
SET @refno:=0;
SET @row:=0;
SELECT datetime, refno, status, RN
FROM (
      SELECT datetime,
            case when @refno<>refno then @row:=0 else null end _row_reset,
            @refno as old_refno, 
            @refno:=refno as refno,
            status,
            @row:=@row+1 as RN
      FROM orders
      ORDER BY refno asc, datetime desc
                                                     ) X
WHERE RN=1 AND status<>'delivered'     

The above query will group your records according to the reference no and then take the latest record for each reference number and check if it's not equal to delivered.
Thanks for the help and suggestions insac

