Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: Rank function in Sybase 15.0.2

    Hi All,

    I am using Sybase 15.0.2 and found that RANK() doesn't work. Is there any other alternative. Please help.

    TIA
    Kapil

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use a temp table with identity column
    Code:
    -- test data --
    select * into #t1 from (select 
    1 , 20 , 'one'   union all select
    3 , 30 , 'tree'  union all select
    2 , 10 , 'two'   union all select
    4 , 20 , 'four' )f(
    id, amt, name   )
    
    -- rank --
    select id=identity(9) , amt into #t2 from #t1 order by amt
    select b.rank, a.*
    from #t1 a
    join (select rank=min(id) ,amt from #t2 group by amt) b
      on a.amt=b.amt
    order by b.rank
    
    
    -- dense rank --
    select dense_rank=identity(9) ,amt into #t3 from #t1 group by amt order by amt
    select b.dense_rank, a.*
    from #t1 a
    join #t3 b
      on a.amt=b.amt
    order by b.dense_rank
    
    -- clean up --
    drop table #t3
    drop table #t2
    drop table #t1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •