CODEWITHSUNDEEP
mysqlsql
Yasmin Líbano
Yasmin Líbano

Reputation: 71

Get the distinct rows with oldest date

I have a table like this:

MyTable:
   id: pk
   numero: varchar
   data_modif: timestamp
   ...

I have multiple records with same value in numero and I need to return each distinct numero record with oldest data_modif. How can I do this?

Upvotes: 1

Views: 595

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This sounds like aggregation:

select numero, min(data_modif)
from mytable
group by numero;

If you want the entire row, then window functions are one method:

select t.*
from (select t.*,
             row_number() over (partition by numero order by data_modif asc) as seqnum
      from mytable t
     ) t
where seqnum = 1;

EDIT:

In an old version of MySQL, you woudl use:

select t.*
from t
where t.data_modif = (select min(t2.data_modif)
                      from t t2
                      where t2.numero = t.numero
                     );

Upvotes: 2

Related Questions