delete from table where exists (select lot_no,min(date) from table group by lot_no having count(*) > 1)

is this correct?
but i'm delete all the data in the table?

If you run this:
Are the returned results what you want to delete? I don't think the min(date) bit is what you want.

yes this is the result that i wanted to delete.

because i have two records i want to keep the latest date so that is why i wanted it to be delete and i will use it for another complex query so that i why i don't want it to my table.

try:

delete from table where (lot_no,date) in (select lot_no,min(date) from table group by lot_no having count(*) > 1);


but caution:
- if both rows have the same date, both will be deleted
- if there are more than two rows with same lot_no, only that with the oldest date will be deleted


<edit> maybe this is a better solution:

delete from table a where exists ( select * from table b where a.lot_no = b.lot_no and a.date < b.date )

</edit>