please help me :-) This is absolutely new for me and i have no idea how to do this.

I have MySQL table with this structure and these values:

I need to display all rows where material_referenceid = (for example) 13 and date = WHOLE OLDEST DAY value in table. So from this table i would like to get rows with material_id = 2, 27. When these two deleted, will select 10.

Many thanks in advance for everybody who will help me with this. Really appreciate your help!

CodePudding user response：

You just need to select the oldest day (in a subquery) and join that back to the table.

To select the oldest day:

select date(min(material_cas)) from material where material_referenceid = 13

So, using that:

select m.material_id, m.material_cas
from (
   select date(min(material_cas)) oldest_date from material where material_referenceid = 13
) oldest
join material m on m.material_reference_id=13 and date(m.material_cas)=oldest_date

Assuming material_cas is stored in UTC, to look based on the Europe/Prague date, do instead:

select m.material_id, m.material_cas
from (
   select date(convert_tz(min(material_cas),' 00:00','Europe/Prague')) oldest_date from material where material_referenceid = 13
) oldest
join material m on m.material_reference_id=13 and date(convert_tz(m.material_cas,' 00:00','Europe/Prague'))=oldest_date