Mysql Retrieve Specific Number of grouped rows from a join

Question

So I created this query

SELECT a.email
     , a.password
     , a.server
     , a.status
     , a.step
     , b.*
  FROM mail a 
  JOIN automation_logs b 
    ON b.accountid = a.accountid 
 WHERE a.status <0 
 ORDER 
    BY b.created DESC;

What it's used for is essentially I'm trying to get debug information from our log table for email accounts that are errored out on our api. The automation_logs table essentially is text dump of error messages that were produced by our API.

This query gives me what I want but gives me too much. I only want the last five most recent (read: created field is a timestamp) error messages for any of the accounts as some of these accounts have existed for years and old errors have been fixed and aren't necessary to know what's broken and just pad the results.

Is this possible with a single query?

take a look at this answer https://stackoverflow.com/a/30269273/2134021 — Marshall Tigerus, Aug 09 '17 at 14:52
@perumalN How else would I specify that I want every column from the other table? — Cody Savage, Aug 09 '17 at 17:02
@Myonara I'm looking to limit the amount of rows from the joining table, not the entire result. LIMIT doesn't work unfortunately. — Cody Savage, Aug 09 '17 at 17:03
You can put the query on b into a subquery, ordering and limiting it there and joining then the whole table. Without a http://sqlfiddle.com/ or more information on both tables including some sample data it'S hard to develop the right solution. — Myonara, Aug 09 '17 at 17:39
@MarshallTigerus Thank you, this is exactly what I needed. I'll post my solution below. — Cody Savage, Aug 09 '17 at 17:46

score 0 · Accepted Answer · answered Aug 09 '17 at 17:54

Following the advice listed here: Thank you Marshal Tigerus

I was able to get it to work with the following sql:

SET @currcount = NULL, @currvalue = NULL;

SELECT a.email,a.password,a.serverid,a.status,a.step,b.message,b.created FROM mail as a JOIN (
    SELECT * FROM (
        SELECT
            *,
            @currcount := IF(@currvalue = accountid, @currcount + 1, 1) AS num,
            @currvalue := accountid AS cur_id
        FROM automation_logs
        WHERE message NOT LIKE ""
        ORDER BY accountid, created DESC
    ) as limitedresults WHERE num <= 5
) as b ON a.accountid = b.accountid WHERE a.status < 0 ORDER BY b.created DESC

Mysql Retrieve Specific Number of grouped rows from a join

1 Answers1