 <p>Use <code>group by</code> and <code>join</code>:</p> <pre><code>SELECT s.lot_number, s.prod_code, s.date_received, s.expiry_date, s.quantity FROM scheme.stquem s JOIN (SELECT lot_number, min(date_received) as mindr FROM scheme.stquem WHERE prod_code = '001' AND lot_number &lt;&gt; '' //removes blanks GROUP BY lot_number ) sl ON sl.lot_number = s.lot_number and sl.mindr = s.date_received WHERE prod_code = '001' ORDER BY s.lot_number; </code></pre>