CODEWITHSUNDEEP
mysql
Julien698
Julien698

Reputation: 716

How to get row number in select query mysql

I try to get all the years in a table with 174 rows. There is two differents years 2016 and 2017. And add to the query's response and Id which correspond at the "row count" of the response.

Here is the query :

SET @n=0;
SELECT YEAR (`Date`) AS Libelle, @n := @n + 1 AS id FROM Datas GROUP by Libelle

The response is : 

|Libelle| Id|
|   2016|  1|
|   2017|174|

What's the way to get :

|Libelle| Id|
|   2016|  1|
|   2017|  2|

174 corresponds to the last record in my table. And I understand that's @n is incremented with all other rows.

Have you an idea to do this ?

Upvotes: 0

Views: 3364

Answers (2)

marpe
marpe

Reputation: 198

Something like this perhaps?

SELECT
    t1.Libelle,
    @n := @n + 1 AS id
FROM (
    SELECT DISTINCT YEAR(`Date`) AS Libelle
    FROM Datas
    GROUP BY Libelle
) t1,
(
    SELECT @n := 0
) t2;

Related: https://stackoverflow.com/a/4474389/4152813

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

try this. so the counter works only on the result:

SELECT  @n := @n + 1 AS id , tmp.*
FROM (
  SELECT DISTINCT YEAR (`Date`) AS Libelle FROM Datas GROUP by Libelle ) AS tmp,
  ( SELECT  @n := 0) as parameter;

Upvotes: 1

Related Questions