How to show rows in packs of three of two tables in MySQL

Question

This is a follow-up question to How to show rows in packs of three in MySQL

I have two tables:

mytable

ID  Type  Timestamp
1   A     101   
2   A     102
3   B     103
4   B     104   
5   A     105   
6   B     106   
7   A     107
8   A     108
9   B     109   
10  A     110   
11  B     111
12  B     112


mytable 2

ID2  Text  Status
1    x     1   
2    x     1
3    y     1
4    y     1   
5    x     1   
6    y     1   
7    x     1
8    x     1
9    y     1   
10   x     0   
11   y     1
12   y     0

I want to show a result sorted by Type and Timestamp where every 3 rows the Type changes like this (rows with Status=0 are skipped):

ID  Type  Timestamp  Text
1   A     101        x
2   A     102        x
5   A     105        x
3   B     103        y
4   B     104        y
6   B     106        y
7   A     107        x
8   A     108        x
9   B     109        y
11  B     111        y

I tried this:

SELECT id, type, timestamp, text  
FROM (
SELECT 
    t.*,t2.text,t2.id2, 
    @rn := CASE WHEN @type = type THEN @rn + 1 ELSE 1 END rn,
    @type := type
FROM 
    mytable t, mytable2 t2
    WHERE t2.id2=t.id
    AND status=1
    CROSS JOIN (SELECT @type := NULL, @rn := 1) x
ORDER BY type, timestamp
) x

ORDER BY 
FLOOR((rn - 1)/3),
type, 
timestamp;

[Demo on DB Fiddle] (https://www.db-fiddle.com/f/7urWNPqXyGQ5ANVqxkjD7q/1)

This is your answer: https://www.db-fiddle.com/f/7urWNPqXyGQ5ANVqxkjD7q/5 — Madhur Bhaiya, Oct 01 '19 at 14:03

GMB · Accepted Answer · 2019-10-01T14:12:58.883

To start with, yo would just need to fix your JOIN, whose syntax is not correct (typically, the WHERE clause goes after all JOINs, and we prefer explicit joins over implicit joins).

Also, as commented by Madhur Bhaiya, using filters with incremented variables is tricky. For this to work properly, we would need to join the two tables in a subquery, and do the ordering inside the subquery itself, and then proceed with the variable logic.

Consider:

SELECT id, type, timestamp, text  
FROM (
SELECT 
    t.*,
    @rn := CASE WHEN @type = type THEN @rn + 1 ELSE 1 END rn,
    @type := type
FROM 
    (
        SELECT t.*, t2.text,t2.id2
        FROM mytable t
        INNER JOIN mytable2 t2 ON t2.id2=t.id AND status=1
        ORDER BY type, timestamp
    ) t
    CROSS JOIN (SELECT @type := NULL, @rn := 1) x
) x
ORDER BY FLOOR((rn - 1)/3), type, timestamp;

Demo on DB Fiddle:

| id  | type | timestamp | text |
| --- | ---- | --------- | ---- |
| 1   | A    | 101       | x    |
| 2   | A    | 102       | x    |
| 5   | A    | 105       | x    |
| 3   | B    | 103       | y    |
| 4   | B    | 104       | y    |
| 6   | B    | 106       | y    |
| 7   | A    | 107       | x    |
| 8   | A    | 108       | x    |
| 9   | B    | 109       | y    |
| 11  | B    | 111       | y    |

Does not work: https://www.db-fiddle.com/f/7urWNPqXyGQ5ANVqxkjD7q/3 You will need to do one more reading ;) . Check this: https://stackoverflow.com/a/53465139/2469308 — Madhur Bhaiya, Oct 01 '19 at 14:01
@MadhurBhaiya: ah yes... mixing variables and joins is tricky indeed. Thanks for this other reading! I updated my answer accordingly. — GMB, Oct 01 '19 at 14:11
Thank you very much @MadhurBhaiya and @GMB! Works like a charm :) — mechs, Oct 01 '19 at 16:53

How to show rows in packs of three of two tables in MySQL

1 Answers1

Linked