Rank function in Sybase ASE to get the top n from each group

Question

I referred to this question

I am trying to get the top 5 sequence of each id. I am using Sybase Ase 16.0 and i believe Rank/Partition is not supported

My expected output is something like

id sequence
-- --------
1 1
1 2
1 3
1 4
1 5
2 1
2 2
3 1
3 2
3 3

I tried the below, but is not sure i get the output like above in sybase

select sysprocedures.id, sysprocedures.sequence,
(select count('x') from sysprocedures t2 where sysprocedures.id = t2.id and sysprocedures.sequence < t2.sequence) as myrank from sysprocedures;

Answer 1

sysprocedures.sequence numbering starts with 0 so we can use this to our advantage in the where clause:

select  id,
        sequence
from    sysprocedures
where   sequence <= 4
order by 1,2
go

Running this in the master database generates:

 id          sequence
 ----------- -----------
   889051172           0
   889051172           1
   889051172           2
   889051172           3
   889051172           4
   921051286           0
   921051286           1
   921051286           2
   921051286           3
   921051286           4
   ... snip ...

NOTE: verified against an ASE 16.0 SP04 PL04 instance

---

Assuming a) the sysprocedures query is an attempt at a minimal example and therefore b) the proposed answer does not work with OP's real world data ... OP may want to ask a new question and provide a more realistic set of data and explanation. For example:

• the minimum sequence isn't always the same number (eg, id=1 has a min(sequence)=3, id=7 has a min(sequence)=17, etc)
• there could be gaps in a series of sequences for a given id (eg, id=1 sequence series is 3, 4, 5, 36, 42, 126)

Answer 2

Per a comment from OP we're told the sysprocedures example is not representative of OP's actual data (so my other answer is likely not usable with OP's real data), so this brings us back to the question of how to implement windowing functions (eg, rank() / row_number()) in ASE ...

---

One idea that expands on an answer to the question OP linked to:

---------- if you only need id/sequence

select  dt.id,
        dt.sequence

from    (select s2.id,
                s2.sequence,
                (select count(*) + 1
                 from   sysprocedures s3
                 where  s3.id       = s2.id
                 and    s3.sequence < s2.sequence) as rownum
        from    sysprocedures s2) dt

where   dt.rownum  <= 5
order by dt.id, dt.sequence

---------- if you need additional columns from sysprocedures

select  s1.id,
        s1.sequence
--      other s1.* columns
from    sysprocedures s1

join    (select s2.id,
                s2.sequence,
                (select count(*) + 1
                 from   sysprocedures s3
                 where  s3.id       = s2.id
                 and    s3.sequence < s2.sequence) as rownum
        from    sysprocedures s2) dt

on      dt.id       = s1.id
and     dt.sequence = s1.sequence
and     dt.rownum  <= 5
order by s1.id, s1.sequence

Running these in the master database:

 id          sequence
 ----------- -----------
   889051172           0
   889051172           1
   889051172           2
   889051172           3
   889051172           4
   921051286           0
   921051286           1
   921051286           2
   921051286           3
   921051286           4
   ... snip ...

---

Another common approach is to populate a #temp table with the id/sequence plus an identity value, then join the main table with the #temp table to obtain the desired results, eg:

---------- generate unique sequential rownums for all rows

select  id,
        sequence,
        rownum=identity(int)
into    #id_seq
from    sysprocedures
order by 1,2

---------- if you only need id/sequence

select  is1.id,
        is1.sequence
from    #id_seq is1
where   is1.rownum  <= (select   min(rownum) + 4    -- find top 5
                        from     #id_seq is2
                        where    is2.id = is1.id
                        group by is2.id)
order by is1.id, is1.sequence

---------- if you need additional columns from sysprocedures

select  s.id,
        s.sequence
--      other s.* columns
from    sysprocedures s
join    #id_seq is1
on      is1.id       = s.id
and     is1.sequence = s.sequence
and     is1.rownum  <= (select   min(rownum) + 4    -- find top 5
                        from     #id_seq is2
                        where    is2.id = is1.id
                        group by is2.id)
order by s.id, s.sequence

Running these in the master database:

 id          sequence
 ----------- -----------
   889051172           0
   889051172           1
   889051172           2
   889051172           3
   889051172           4
   921051286           0
   921051286           1
   921051286           2
   921051286           3
   921051286           4
   ... snip ...

NOTE: all queries verified against an ASE 16.0 SP04 PL04 instance