Mysql group with minimal

Question

The table data is given below (a small part), basically I'm looking for queries only rows with a minimum value of original_date_ctr when grouping by accoutn number.

I tried using HAVING (MIN ()) and where = Min () and other ways with no luck.

The correct result here will give me id_ctr 688, 1204 and 1209

id_ctr account_number_cus original_date_ctr mrc_ctr ------ ------------------ ----------------- ---------- 688 20062 2008-05-17 138.97 1204 151604 2006-08-10 42000.00 1209 151609 2006-06-29 968.68 1367 20062 2011-10-27 207.88 1434 151609 2009-09-10 1469.62 1524 151604 2009-09-01 36999.99 1585 151609 2012-05-31 1683.88 

Answer 1

You can do it as follows:

 select t1.id_ctr, t1.account_number_cus, t1.original_date_ctr, t1.mrc_ctr from yourtable t1 inner join ( select min(original_date_ctr) as mindate, account_number_cus from yourtable group by account_number_cus ) t2 on t1.account_number_cus = t2.account_number_cus and t1.original_date_ctr = t2.mindate 

see SQL Fiddle with Demo

Answer 2

Doing this with the connection will be faster:

 SELECT a.* FROM mytable a LEFT JOIN mytable b ON a.account_number_cus = b.account_number_cus AND b.original_date_ctr < a.original_date_ctr WHERE b.id_ctr IS NULL