CODEWITHSUNDEEP
sqlindexingduplicatesms-access-2010
Sero
Sero

Reputation: 3

re-indexing duplicate rows

Hi I have a table below;

ID  length
1   1050
1   1000
1   900
1   600
2   545
2   434
3   45
3   7
4   5

I need an SQL code to make the below table

ID  IDK length
1   1   1050
1   2   1000
1   3   900
1   4   600
2   1   545
2   2   434
3   1   45
3   2   7
4   1   5

IDK is the new column to reindexing the same ID according to ascending order of length. Thank you very much

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is a pain in MS Access. Here is one way using a correlated subquery:

select t.*,
       (select count(*)
        from foo as t2
        where t2.id = t.id and t2.length >= t.length
       ) as idk
from foo as t;

Upvotes: 2

Related Questions