Results 1 to 7 of 7

Thread: dense_rank()

  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Unanswered: dense_rank()

    hi, i need a query similar at the below which is for Oracle. I try to give id to every materail with grouping them by material. I see that there is no dense_rank() function in ASE 12.5. I really need your ideas. How can I handle this?


    SELECT name, material, DENSE_RANK()
    OVER (PARTITION BY name ORDER BY material) AS grup_ici_no
    FROM tablo ;

    NAME MATERIAL GRUP_ICI_NO
    -------- -------- -----------
    a x 1
    a y 2
    b z 1

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Most of the folks in this section know Sybase and not Oracle so it might help if you told us what DENSE_RANK() does! For that matter I was stumped by the OVER and the PARTITION BY bits as well

  3. #3
    Join Date
    Jul 2008
    Posts
    5
    I also use sybase not oracle. but when i was searching I see a solution in Oracle.

    What I want to do is ; giving id to records with grouping.

    name sirname collage id
    a b k 1
    c d k 2
    e f m 1
    g h n 1


    for example here ı grouped with collage. 2 people in "k". so one of them get 1 and the other 2. if there were n people. Ids supposed to be 1..n

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    Most of the folks in this section know Sybase and not Oracle so it might help if you told us what DENSE_RANK() does! For that matter I was stumped by the OVER and the PARTITION BY bits as well
    DENSE_RANK is one of the standard window functions introduced into the sql-2003 standard

    microsoft sql server has them too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Thanks for the info Rudy - I'd never heard of the function.
    Is this only available on certain versions of Sybase?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    Is this only available on certain versions of Sybase?
    no idea, i don't use sybase

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Besides the info posted by r937
    Here is a link to the DENSE_RANK function implemented in the Sybase products
    Sybase IQ and SQL Anywhere

    In ASE 12.5 you'll have to do e.g.
    Code:
    select id=identity(9),* into #t1 from  (select 
    'e','es', 'z' union all select
    'c','cs', 'x' union all select
    'b','bs', 'z' union all select
    'f','fs', 'z' union all select
    'g','gs', 'y' union all select
    'a','as', 'x')testdata(name, surname, collage)
    
    select name, surname, collage, rank=
    (select count(*) from #t1 b
     where b.collage=a.collage
       and b.id<=a.id
    ) 
    from #t1 a
    order by collage, rank
    
    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
  •