How to get row number in select query mysql

Question

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 ?

Bernd Buffen · Accepted Answer · 2016-03-22 14:55:55Z

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;

edited Mar 22, 2016 at 14:55

answered Mar 22, 2016 at 14:45

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julien698 Over a year ago

It says that there is an error in the last line "(@n :=0) as parameter"

Community · Accepted Answer · 2017-05-23 11:45:09Z

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

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Mar 22, 2016 at 14:58

marpe

19810 bronze badges

Collectives™ on Stack Overflow

How to get row number in select query mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related