0

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 ?

Julien698
  • 676
  • 3
  • 10
  • 27

2 Answers2

1

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;
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
0

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

Community
  • 1
  • 1
marpe
  • 198
  • 10