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

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...

CodePudding user response：

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

CodePudding user response：

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.