Select 2 Tables but I want to limit the rows on the second table

Question

Now I am trying to learn Mysql but I am stuck with this I got two tables

Table Equipment

id  location    machine
1   32          Roam
2   33          Polisher
3   34          Vacuum

Table maintenance

id      maintenance date  entry 
1       07-27-2009          1
1       07-27-2009          2
2       08-22-2015          3
2       08-22-2017          4
3       05-25-2016          5
3       05-25-2018          6

I want to Select the Table 1 with the recent maintenance Date but some of them will have the same date so it will be based on the entry number assuming the most recent entry is the latest maintenace date

id  location    machine  maintenance date   entry
1   32          Roam      07-28-2009          2
2   33          Polisher  08-22-2017          4
3   34          Vacuum    05-25-2018          6

I did research that this can be accomplished by Group by with 2 Select

    Select * From Maintenance, Equipment 
where Maintenance.id = Equipment.id group by maintenance.id Order By Maintenance.Entry DESC

It shows almost want I want but

id  location    machine  maintenance date   entry
1   32          Roam      07-28-2009          1
2   33          Polisher  08-22-2017          4
3   34          Vacuum    05-25-2018          6

Entry is number 1 so it was not the recent service. Did try DESC but it just inverts still not the latest entry

score 0 · Accepted Answer · answered Sep 06 '19 at 04:57

0

You can try this-

Select * From Maintenance m inner join
Equipment on m.id = Equipment.id
where maintenancedate in (select max(maintenancedate) from Maintenance m1
where m.id=m1.id)

answered Sep 06 '19 at 04:57

Fahmi

37,315
5
22
31

Select * From Maintenance m inner join Equipment on m.id = Equipment.id where maintenancedate in (select max(maintenancedate) from Maintenance Group By id) – Kristian Roy Villafania Sep 06 '19 at 05:50

Select 2 Tables but I want to limit the rows on the second table

1 Answers1