Get row number sql

Navigation
1

I've been testing for a couple of days how to get the row number of a query, and in fact I got it but when I upload the code to the server where I have my page hosted, I find a syntax error. I have reviewed the mysql versions and in local I have the version 8.0 while in the server the 5.5

I need to get a query to rescue the most visited places in the world, limiting the number of locations by country and continent. The code that works for me in local is the following: 

select * from
  (select * , row_number() over (partition by  id_continente order by visitas desc) y from
      (select *, row_number() over (partition by  id_pais order by visitas desc) x from lugar_localidades_top_site where id_site=:site and categoria >=:categoria and id_pais <> :pais_excluido) top
    where x <= :loc_por_pais) top_mundo
  where y <= :loc_por_continente
order by visitas desc

The error is in the row_number () function since mysql 5.5 does not implement it. Any ideas to solve the issue? (I can not change the server version)

    
asked by onlydiego22 04.09.2018 в 12:27
source

1 answer

0

If you have the possibility to add a function, put the one that is detailed in this post 

DROP FUNCTION IF EXISTS rownum;
DELIMITER $$
CREATE FUNCTION rownum()
  RETURNS int(11)
BEGIN
  set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);
  set @ranklastrun=CURTIME(6);
  RETURN @prvrownum;
END $$
$$
DELIMITER ;

To use it: 

select rownum() from table
    
answered by 04.09.2018 в 12:53
Implement system remind me Print consecutive rows of the same column mysqli