Home / Questions / 43915
Accepted
Hoose
Asked: 2020-01-14 07:36:19 +0800 CST 2020-01-14 07:36:19 +0800 CST

How to create a query with log counter?

  • 772

How can I create in ONE single query where it returns the first column with the log counter? Or in other words How can I list the results of a single select?

Example:

---------------------------
  No. | Nombre
---------------------------
   1  | Jose Vaca Cipres
---------------------------
   2  | Jesus Gonzales
---------------------------
   3  | Edgar Hernandez
---------------------------
mysql

3 Answers

  1. Best Answer

    Using a custom variable:

    SELECT @rownum:=@rownum+1 ‘nro’,  nombre 
FROM tabla t, (SELECT @rownum:=0) r
    • 6

  2. In this way you can obtain the count of the records that you require

    SET @numero=0;
SELECT @numero:=@numero+1 AS `posicion`, `Emails`.* FROM `Emails`;

    You can refer to the following link for more information. http://totaki.com/poesiabinaria/2013/01/number-the-results-of-a-select-in-mysql/

    • 1

  3. When you use a query the statement with INNER, ORDER BY, LIMITchanges the counter

    SELECT (@REG:= @REG+1) AS cont,p.`Emails`,p.`descripcion`, p.idpersona
from persona p
INNER JOIN ciudad c on c.idpersona=p.idpersona
ORDER BY p.`descripcion` ASC LIMIT 10,10;
    • 1