Mysql Retrieve Specific Number of grouped rows from a join

161
August 09, 2017, at 9:22 PM

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?

Answer 1

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
READ ALSO
Raspberry Pi Database Server

Raspberry Pi Database Server

I would like to host a database on my raspberry pi to which I can access from any deviceI would like to access the contents of the database using python

191
Inserting each array member/element into a table row

Inserting each array member/element into a table row

I have this table that is related to a form that is suppose to store the users answers to a number of question (this number is not set but has a max limit of 30 questions)

125
How to use a MySQL Database with Angular 2/4

How to use a MySQL Database with Angular 2/4

Hi I'm quite new to angular and haven't found much about Angular and databases yet, has someone successfully connected with a MySQL DB via PHP/Ajax?

377