<div>
and like this for the second part<br />

<pre><code id="code-8121943">select a.id, count(*) from categories c
LEFT JOIN (
	select * from cataction t1
	where last_run = (select max(last_run) from cataction where cid = t1.cid)
) ca ON c.id=ca.cid
LEFT JOIN action a ON ca.aid=c.id
group by a.id</code></pre>
</div>


and like this for the second part


select a.id, count(*) from categories c
LEFT JOIN (
	select * from cataction t1
	where last_run = (select max(last_run) from cataction where cid = t1.cid)
) ca ON c.id=ca.cid
LEFT JOIN action a ON ca.aid=c.id
group by a.id

<div>
<div class="content wysiwyg-content">
I have 3 tables and I need to get the most recent row of the middle table in a join.<br />
<br />
Table1 - Categories<br />
ID - PK<br />
Name - Varchar<br />
<br />
Table2 - CatAction<br />
ID - PK<br />
CID - Int FK to ID in Categories<br />
AID - Int FK to ID in Actions<br />
last_run - date/time<br />
<br />
Table 3 - Actions<br />
ID - PK<br />
Name - Varchar<br />
<br />
When an &quot;action&quot; fires it updates the last_run field in CatAction by cid with the time it ran. I need to select all fields but only the last_run action by category. This is NOT what I want, but it shows the relationships of the tables<br />
<br />
select * from categories c<br />
LEFT JOIN cataction ca ON c.id=ca.cid<br />
LEFT JOIN action a ON ca.aid=c.id<br />
<br />
I also need a query that will count the number of actions by type by last run time. So I will need to group by action id as well.
</div>
</div>


I have 3 tables and I need to get the most recent row of the middle table in a join.

Table1 - Categories
ID - PK
Name - Varchar

Table2 - CatAction
ID - PK
CID - Int FK to ID in Categories
AID - Int FK to ID in Actions
last_run - date/time

Table 3 - Actions
ID - PK
Name - Varchar

When an "action" fires it updates the last_run field in CatAction by cid with the time it ran. I need to select all fields but only the last_run action by category. This is NOT what I want, but it shows the relationships of the tables

select * from categories c
LEFT JOIN cataction ca ON c.id=ca.cid
LEFT JOIN action a ON ca.aid=c.id

I also need a query that will count the number of actions by type by last run time. So I will need to group by action id as well.

Get the top row of a group by date in a SQL join

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

MySQL Server

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.