Line numbering

Asked

Viewed 40 times

0

Suppose the following table:

id -- ano

1 -- 2014

2 -- 2014

3 -- 2014

4 -- 2015

5 -- 2015

6 -- 2016

7 -- 2016

8 -- 2016

9 -- 2016

I would like to get a list with a sequential count/numbering of rows grouped per year, as follows in the example:

id -- ano -- count

1 -- 2014 -- 1

2 -- 2014 -- 2

3 -- 2014 -- 3

4 -- 2015 -- 1

5 -- 2015 -- 2

6 -- 2016 -- 1

7 -- 2016 -- 2

8 -- 2016 -- 3

9 -- 2016 -- 4

Note that this is not a simple count, where a Count+group by would solve, but rather a line numbering, where each year the counter goes back to 1.

Any idea how to do that?

mysql
  • 1

    This can be done directly in the programming language instead of in the query, it is probable until it becomes more performatic when adjusting in the loop, which language uses?

  • I’m using c#, but I managed to reach a solution using variables in mysql that I will post now.

  • C# with what? Asp.net-mvc or MySqlDataAdapter.fill() or netcore with Entityframeworkcore? If you can give some relevant detail so that it is possible to formulate a suggestion this will facilitate.

2 answers

4

Depending on the version of Mysql you are using, just use the window Function together with the function ROW_NUMBER().

SELECT 
  id, 
  ano,
  ROW_NUMBER() OVER (PARTITION BY ano) AS posicao
FROM anos
ORDER BY ano, id

The output generated would be, for the given example:

| id  | ano  | posicao |
| --- | ---- | ------- |
| 1   | 2014 | 1       |
| 2   | 2014 | 2       |
| 3   | 2014 | 3       |
| 4   | 2015 | 1       |
| 5   | 2015 | 2       |
| 6   | 2016 | 1       |
| 7   | 2016 | 2       |
| 8   | 2016 | 3       |
| 9   | 2016 | 4       |

0

I was able to get the solution using variables. Maybe not the most elegant solution, but it met what I needed with a very good performance, because my list has almost 1 million lines.

SET @y = 0;
SET @c = 0;

select id, ano
,if (@y <> ano, @c := 1, @c := @c+1) as numeracao
,@y := ano  -- só nao sei ainda como setar a variavel dentro
            -- do select sem exibi-la no resultado.
from __anos;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.