i have this query which works but only gives the first five rows from the tables. see my former question -> multiple SELECT statements using CTE
WITH
cte AS ( SELECT n.name,
e.value,
ROW_NUMBER() OVER (PARTITION BY e.value
ORDER BY e.id) AS rn
from entries e
LEFT JOIN nodes n on n.id=e.node_id
LEFT JOIN attribs a on a.id=e.attrib_id
WHERE a.name = 'LOCATION'
AND e.value IN ('Wienerberg', 'Gruberstrasse')
AND DATE(ts) = CURRENT_DATE
ORDER BY e.id
),
nums AS ( SELECT 1 rn UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
)
SELECT t1.name LNZ, t2.name WBG
FROM nums
LEFT JOIN cte t1 ON nums.rn = t1.rn
LEFT JOIN cte t2 ON nums.rn = t2.rn
WHERE t1.value = 'Gruberstrasse'
AND t2.value = 'Wienerberg'
-- AND COALESCE(t1.name, t2.name)
ORDER BY nums.rn
+----------------------+----------------------+
| LNZ | WBG |
+----------------------+----------------------+
| AIXVAEBDBT | KUG01148_JBOSS-T6 |
| OOEGKKT6 | AIXMVBMIGTA2 |
| HSR5S1P8_AM | KUG01115_WSAP_HA_LPM |
| AIXSTP11R3APP | AIXTESTHA2C1_HA_LPM |
| HSR3S1P10_OOEGKKTEST | KUG01142_STP17PR_HA |
+----------------------+----------------------+
how to i get all the rows without limiting to 5 rows? i skimmed through the mysql cte docs, but this seems to complex for me.