Rudolf Rudolf - 1 month ago 6
MySQL Question

Select min date with another table columns

I want to select last created field for user rate. Below are links to screenshots of tables from which i try to select data.

rates

fields

My query is

select fields.content, min(fields.creation_date) as created
from fields
left join rates
on fields.rate_id=rates.rate_id
where rates.user_id=4
group by fields.content


and result is

content              created                            

dolor sit             2016-10-28 19:33:50       

lorem ipsum      2016-10-28 19:33:49       

How to modify my query to have result like below?

content              created                            

lorem ipsum      2016-10-28 19:33:49       

Answer

Use order by and limit:

select f.*
from fields f left join
     rates r
     on f.rate_id = r.rate_id
where r.user_id = 4
order by f.creation_date asc
limit 1;

Note that if there are ties (two records with the same creation_date), then this will choose one arbitrarily.

Also, I suspect that you want:

select f.*
from rates r left join
     fields f
     on f.rate_id = r.rate_id
where r.user_id = 4
order by f.creation_date asc
limit 1;

In your version, the left join is not necessary -- it is turned to an inner join by the where clause. Further, I would expect f.rate_id to always match r.rate_id, especially if you have a properly declared foreign key relationship. This will return a row even if there are no records in fields (which appears to be your intention).