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

Question

I got two tables:

Processes

idProcess	data
1	XXXX
2	XXXX
...	...

Tracings:

idTrace	idProcess
1	1
2	1
3	1
4	2
5	2
6	2
7	2
...	...

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

idTrace	idProcess
3	1
2	1
7	2
6	2
...	...

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

Never use an `id` column to infer recency of a record. There are Many reasons that an id may be allocated out of sequence. An id is a surrogate key, it's Sole responsibility is to uniquely identify a row. Anything else is an anti-pattern. Always use another column such as a timestamp or other data for recording ordering. — MatBailie, Sep 29 '21 at 18:36
Is this mysql 8 or 5.x? They each have different approaches. — MatBailie, Sep 29 '21 at 18:38
Is mysql 5.x, tracings has a timestamp colum, thanks for the observation!! — Diego La Rosa, Sep 29 '21 at 18:48

ggordon · Accepted Answer · 2021-09-29T20:55:04.650

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;

idTrace	idProcess
3	1
2	1
7	2
6	2

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;

idTrace	idProcess
3	1
2	1
7	2
6	2

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;

idTrace	idProcess	data
3	1	XXXX
2	1	XXXX
7	2	XXXXX
6	2	XXXXX

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

works perfect, but don't mention it has to be for mysql 5.x, sorry and thanks!! — Diego La Rosa, Sep 29 '21 at 18:53
@DiegoLaRosa I updated the answer with a similar approach that works with mysql 5.x and working demo db fiddle. Let me know if this works for you. — ggordon, Sep 29 '21 at 20:50
I noted that in a comment you mentioned that `tracings` had a timestamp column. You may modify the subquery of the mysql 5.x answer to `FROM Tracings ORDER BY idProcess,TimestampColumn DESC` to adjust for this. — ggordon, Sep 29 '21 at 20:57

MatBailie · Answer 2 · 2021-09-29T18:53:10.460

0

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

edited Sep 29 '21 at 18:53

answered Sep 29 '21 at 18:41

MatBailie

83,401
18
103
137

Thank you for the answer, in case that tracings has only one row for process i need at least that row. I test this in your fiddle and with one row in tracing by process shows nulls values. – Diego La Rosa Sep 29 '21 at 19:14

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

2 Answers2