CODEWITHSUNDEEP
mysqlsql
Jack Yuan
Jack Yuan

Reputation: 51

How to make this sql query compatible for older verisons of mysql such v.5.5 and above?

I have this query it works on MySQL 8.0 but not on the below versions like MySQL 5.5. How to make this compatible as I am getting this error on web server: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by pid order by age' at line 11

SELECT p.id,  p.pid, p.name, 
       MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1,
       MAX(CASE WHEN t.dr = 1 THEN age END) as age1,
       MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2,
         MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2,
            MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3,
         MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3,
             MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4,
         MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4
FROM Table1 p
join (select id , pid, name, age,  DENSE_RANK() OVER (partition by pid order by age) as dr
from Table2) t on p.pid= t.pid
group by p.id, p.pid, p.name

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d324e7654218dae3d1168df2bf529099

Upvotes: 0

Views: 38

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

 select version();
version()
5.5.62

Schema:

 CREATE TABLE Table1
     (id int , name varchar(15) , pid int   )

 INSERT INTO Table1
  values   (1, 'Jack', 1521), 
  (2, 'Steve',1522)

 CREATE TABLE Table2
     (id  int, pid int, name varchar(15), age int);
 
 INSERT INTO Table2
    
 VALUES
     (1, 1521, 'John', 12),
     (2, 1521, 'Maria', 7),
     (3, 1521, 'Larry', 3),
     (4, 1522, 'Harry', 5)

Query:

 SELECT p.id,  p.pid, p.name, 
        MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1,
        MAX(CASE WHEN t.dr = 1 THEN age END) as age1,
        MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2,
          MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2,
             MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3,
          MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3,
              MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4,
          MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4
 FROM Table1 p
 join (select id , pid, name, age,  (select count(*) from Table2 t2
 where  t.pid=t2.pid and t2.age<=t.age) as dr
 from Table2 t) t on p.pid= t.pid
 group by p.id, p.pid, p.name

Output:

id pid name name1 age1 name2 age2 name3 age3 name4 age4
1 1521 Jack Larry 3 Maria 7 John 12 null null
2 1522 Steve Harry 5 null null null null null null

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

One method is a correlated subquery to replace DENSE_RANK():

FROM Table1 p JOIN
     (SELECT t2.*,
             (SELECT COUNT(DISTINCT tt2.age)
              FROM table2 tt2
              WHERE tt2.pid = t2.pid AND
                    tt2.age <= t2.age
             ) as dr
      FROM Table2 t2
     ) t 
     ON p.pid = t.pid

Here is a db<>fiddle.

Upvotes: 1

Related Questions