CODEWITHSUNDEEP
mysql
user1777122
user1777122

Reputation: 77

Is there a simple way join rows by their row number in mysql?

So, I have two tables, the first has e.g. 4 records, and the second one contains e.g. two rows. They are not related in any way, just want them to be shown together in a way shown on the picture.enter image description here I know it could be done by joining them by calculating their row number and using those, but I am curious if is there a way to do the same thing without using a variable. Thank you for your advise, and sorry for my english :)

Upvotes: 1

Views: 3383

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Yes, there is a way to do it without using variables, just stright SQL
See this demo: http://www.sqlfiddle.com/#!2/2eeb2/4

Unfortunately, MySql doesn't implement analytic functions like Oracle, Postgre and MS-SQL:
ROW_NUMBER() OVER (partition by ... order by ... ) nor rownum pseudocolumn like Oracle, and a performace of queries like this in MySql is very poor.

SELECT *
FROM (
  SELECT emp.*,
       (SELECT count(*)
        FROM emp e
        WHERE e.empno <= emp.empno
        ) rownum
  FROM emp
) e
LEFT JOIN (
   SELECT dept.*,
         (SELECT count(*)
          FROM dept d
          WHERE d.deptno <= dept.deptno
          ) rownum
   FROM dept
) d
ON e.rownum = d.rownum;

Upvotes: 2

Related Questions