Order query (1,2,3,1,2,3.1 ...)

Question

Order query (1,2,3,1,2,3.1 ...)

Navigation

#1 by (2 votes)

5

How about I have a table students with a column called " id_schools " in Mysql I want it is to make a "select" that throw me the results ordered as follows:

nombre_a         id_escuelas 
---------       -------------
  Juan               1 
  Roman              2
  Raul               3
  Omar               1
  Jose               2

I found in the following link ( link ) A piece of code that adapts it to my table in the following way:

SELECT x.id_escuelas,
       x.nombre_a
FROM (SELECT alumnos.id_escuelas,
             alumnos.nombre_a,
             CASE
                WHEN @id_escuelas != alumnos.id_escuelas THEN @rownum := 0
                WHEN @id_escuelas = alumnos.id_escuelas THEN @rownum := @rownum + 1
                ELSE @rownum
              END AS rank,
              @id_escuelas := alumnos.id_escuelas
         FROM TABLE alumnos,
              (SELECT @rownum := 0, @id_escuelas
      ORDER BY alumnos.id_escuelas) r) x
ORDER BY x.rank, x.id_escuelas

But it throws me errors:

If I delete the word table:

Thank you in advance

mysql query select

asked by Jess182 08.05.2018 в 18:03

source

1 answer

doubt on how a class is structured in java Run php script and send arguments from node.js

score 2 · Answer 1

With some adjustments, the query works:

SELECT
  /*x.id_escuelas,
  x.nombre_a*/
  'x'.'nombre_a',
  'x'.'id_escuelas'
FROM (
  SELECT
    'alumnos'.'id_escuelas',
    'alumnos'.'nombre_a',
    CASE
      WHEN @'id_escuelas' != 'alumnos'.'id_escuelas'
        THEN @'rownum' := 0
      WHEN @'id_escuelas' = 'alumnos'.'id_escuelas'
        THEN @'rownum' := @'rownum' + 1
      ELSE @'rownum'
    END AS 'rank',
    @'id_escuelas' := 'alumnos'.'id_escuelas'
  -- FROM TABLE
  FROM
    'alumnos',
    /*(SELECT @rownum := 0, @id_escuelas
    ORDER BY alumnos.id_escuelas) r) x*/
    (SELECT @'rownum' := 0, @'id_escuelas' := 0) 'r'
  ORDER BY 'alumnos'.'id_escuelas'
) 'x'
ORDER BY 'x'.'rank', 'x'.'id_escuelas';

See db-fiddle .

I'm not sure about the MySQL version used by you. The example was elaborated on MySQL 5.7.17.

If using MySQL 8.0.x, keep in mind that since version 8.0.2 RANK is a reserved word ( 9.3 Keywords and Reserved Words :: R ). Using MySQL 8.0.x you can use the 12.20 Window Functions , that is, something as:

SELECT
  'x'.'nombre_a',
  'x'.'id_escuelas'
FROM (
  SELECT
    'alumnos'.'id_escuelas',
    'alumnos'.'nombre_a',
    ROW_NUMBER() OVER(PARTITION BY 'alumnos'.'id_escuelas') AS 'rank'
  FROM
    'alumnos'
) 'x'
ORDER BY 'x'.'rank', 'x'.'id_escuelas';

See dbfiddle .