Search code examples
sqlsybasesap-asedense-rank

Alternative to dense_rank in sybase ASE

is there a way to rebuild the dense_rank function in Sybase ASE?

So I would need a unique number for every tuple (foo, bar).

Table:

+-----+-----+
| foo | bar |
+-----+-----+
| a   | a   |
| a   | b   |
| a   | c   |
| a   | c   |
| b   | a   |
| b   | a   |
+-----+-----+

Result:

+-----+-----+------+
| foo | bar | rank |
+-----+-----+------+
| a   | a   |    1 |
| a   | b   |    2 |
| a   | c   |    3 |
| a   | c   |    3 |
| b   | a   |    4 |
| b   | a   |    4 |
+-----+-----+------+

How can I do this without the dense_rank function?

Thanks very much!

Solution

  • The following subquery should provide the same functionality:

    select t.*,
       (select 1 + count(distinct foo + ':' + bar)
        from table t2
        where t2.foo < t.foo or
              t2.foo = t.foo and t2.bar < t.bar
       ) as rank
from table t;