0

I have the following table, which I've made very simple because I do not know how to format it as a table on here (side note if anyone could link me to an easy tutorial on that I would be forever grateful).

id
1
1
1
2
2
2

I'd like to add another column which increments in number only on distinct IDs so the outcome should be

Id
1
1
1
2
2
2

rowNum 
1
1
1
2
2
2

Currently all I can manage to get is:

id
1
1
1
2
2
2
rowNum
1
2
3
4
5
6

I'm missing something very simple here as I'm confident I should be able to solve this issue using either row_number or rank and a window function but I cannot figure it out.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
MPJ567
  • 521
  • 5
  • 16

2 Answers2

3

Use DENSE_RANK() instead of ROW_NUMBER():

SELECT
    id,
    DENSE_RANK() OVER (ORDER BY id) dr
FROM yourTable

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • That was it! In this case, this wouldn't be considered a window function then, correct? Would just like to know so I get my terminology straight in the future. – MPJ567 Jun 13 '17 at 01:08
  • 1
    `DENSE_RANK()` is a window function actually. Anytime you see `OVER` you should read "window function." Have a look [here](https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle) for a comparison of row number to dense rank. – Tim Biegeleisen Jun 13 '17 at 01:09
1

You can do this with a subquery self join, as well.

mysql> select id, 
> (select count(distinct id) 
>  from 
>  testtest b 
>  where b.id < a.id) 
> from testtest a;
+------+---------------------------------------------------------------+
| id   | (select count(distinct id) from testtest b where b.id < a.id) |
+------+---------------------------------------------------------------+
|    1 |                                                             0 |
|    1 |                                                             0 |
|    1 |                                                             0 |
|    2 |                                                             1 |
|    2 |                                                             1 |
|    2 |                                                             1 |
+------+---------------------------------------------------------------+
6 rows in set (0.01 sec)

And one more way:

select a.id, b.idRank
from testtest a,
   (
   select id, 
   rank() over 
      (order by id) as idRank
   from (
         select distinct id 
         from testtest
        ) testtest2
   ) b
where a.id = b.id
Bryan Newman
  • 621
  • 3
  • 9