Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: max rank() in a single query

    Hi guys,

    Is it possible to to get the the MAX rank number in one query?

    I know the below query doesn't work but is it possible to accomplish the MAX() without doing a sub-query. The rank function does not work in the HAVING clause.

    SELECT
    accountnumber,
    date,
    MAX(rank () over (partition by accountnumber order by date asc)) AS Rank
    FROM score_table
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nadecian View Post
    without doing a sub-query.
    What's wrong with the subquery?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can apply MAX(also other aggregate funcions and OLAP specificaions) for another OLAP specifications, by adding OVER clause.

    But, please note that OLAP specifications are applied to the result rows of a SELECT clause.
    So, it is usually necessary to put the query in a subquery, to use the result(i.e. MAX funcion) with meaning way.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT workdept
         , edlevel
         ,    RANK()
                 OVER( PARTITION BY workdept
                           ORDER BY edlevel
                     )
           AS rank_edlevel
         , MAX(
              RANK()
                 OVER( PARTITION BY workdept
                           ORDER BY edlevel
                     )
           )  OVER() AS max_rank_edlevel
     FROM  employee
     ORDER BY
           workdept
         , edlevel
    ;

    Result:
    Code:
    WORKDEPT EDLEVEL RANK_EDLEVEL         MAX_RANK_EDLEVEL    
    -------- ------- -------------------- --------------------
    A00           14                    1                    9
    A00           18                    2                    9
    A00           19                    3                    9
    B01           18                    1                    9
    C01           16                    1                    9
    C01           18                    2                    9
    C01           20                    3                    9
    D11           16                    1                    9
    D11           16                    1                    9
    D11           16                    1                    9
    D11           16                    1                    9
    D11           16                    1                    9
    D11           17                    6                    9
    D11           17                    6                    9
    D11           17                    6                    9
    D11           18                    9                    9
    D21           14                    1                    9
    D21           15                    2                    9
    D21           15                    2                    9
    D21           16                    4                    9
    D21           16                    4                    9
    D21           17                    6                    9
    E01           16                    1                    9
    E11           12                    1                    9
    E11           12                    1                    9
    E11           14                    3                    9
    E11           16                    4                    9
    E11           17                    5                    9
    E21           14                    1                    9
    E21           14                    1                    9
    E21           16                    3                    9
    E21           16                    3                    9
    
      32 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT workdept
         , edlevel
         , max_rank_edlevel
     FROM
          (SELECT workdept
                , edlevel
                ,    RANK()
                        OVER( PARTITION BY workdept
                                  ORDER BY edlevel
                            )
                  AS rank_edlevel
                , MAX(
                     RANK()
                        OVER( PARTITION BY workdept
                                  ORDER BY edlevel
                            )
                  )  OVER() AS max_rank_edlevel
            FROM  employee
          )
     WHERE rank_edlevel = max_rank_edlevel
     ORDER BY
           workdept
         , edlevel
    ;
    ------------------------------------------------------------------------------
    
    WORKDEPT EDLEVEL MAX_RANK_EDLEVEL    
    -------- ------- --------------------
    D11           18                    9
    
      1 record(s) selected.
    Last edited by tonkuma; 06-27-12 at 12:05.

  4. #4
    Join Date
    Aug 2011
    Posts
    34
    response to N_I - I am trying to avoid a subquery because I need to do a few of them and would like to cut one of them out.

    Thanks for the help guys!

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the reason for avoiding subqueries? Just because you have many of them is not a very logical reason.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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