Get at least last 2 rows from each row in a joined mysql 5.X tables

I got two tables:

Processes

Tracings:

Need the last two idTrace from each idProcess ordered descending by idTrace:

EDIT
Can be rows in processes that does not exist in tracings yet…

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Since your desired output shared in your question only has columns from your Tracings table you need not use a join but only include your Tracing table for efficiency.

Schema (MySQL v5.5)

The following approach uses variables to determine the order and a where clause to limit by the ordered row number.

SET @row_num:=0;
SET @prev_grp:=NULL;

SELECT
     t.idTrace,
     t.idProcess  
FROM (
    SELECT 
        *, 
        @row_num:=(
             CASE 
                 WHEN @prev_grp<>idProcess THEN 1
                 ELSE @row_num+1
             END
        ) as rn,
        @prev_grp:=idProcess
    FROM 
        Tracings
    ORDER BY 
        idProcess,idTrace DESC
) t 
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

or as one query

SELECT
     t.idTrace,
     t.idProcess  
FROM (
    SELECT 
        *,
        @row_num:=(
             CASE 
                 WHEN @prev_grp<>idProcess THEN 1
                 ELSE @row_num+1
             END
        ) as rn,
        @prev_grp:=idProcess
    FROM 
        Tracings
    CROSS JOIN (SELECT @row_num:=0,@prev_grp:=NULL) as vars 
    ORDER BY 
        idProcess,idTrace DESC
) t 

WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

View on DB Fiddle

Schema (MySQL v8.0)

You may also use ROW_NUMBER to achieve this eg.

CREATE TABLE Processes (
  `idProcess` INTEGER,
  `data` VARCHAR(5)
);

INSERT INTO Processes
  (`idProcess`, `data`)
VALUES
  ('1', 'XXXX'),
  ('2', 'XXXXX');

CREATE TABLE Tracings (
  `idTrace` INTEGER,
  `idProcess` INTEGER
);

INSERT INTO Tracings
  (`idTrace`, `idProcess`)
VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '2'),
  ('6', '2'),
  ('7', '2');

Query #1

SELECT
     idTrace,
     idProcess
FROM (
    SELECT
         *,
         ROW_NUMBER() OVER (
             PARTITION BY idProcess
             ORDER BY idTrace DESC
         ) rn
    FROM
        Tracings
) t
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

Query #2 – if you require data from Processes table

SELECT
     t.idTrace,
     t.idProcess,
     p.data
FROM 
Processes p 
INNER JOIN (
    SELECT
         *,
         ROW_NUMBER() OVER (
             PARTITION BY idProcess
             ORDER BY idTrace DESC
         ) rn
    FROM
        Tracings
) t ON p.idProcess = t.idProcess
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

View on DB Fiddle
Let me know if this works for you.

Method 2

MySQL5.x could use a correlated sub query to find the rows you want to join on.

SELECT
  *
FROM
  Processes   p
LEFT JOIN
  Tracings   t
    ON  t.idProcess = p.idProcess
    AND t.yourTimestampColumn >= (
      SELECT yourTimestampColumn 
        FROM Tracings
       WHERE idProcess = p.idProcess
    ORDER BY yourTimestampColumn DESC
       LIMIT 1 OFFSET 1
    )

Demo : https://www.db-fiddle.com/f/q1YCHFwX3zLiZ6xd52TdN6/0

• Credit to @ggordon, who’s demo I adopted

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0