Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: To get a new column while selecting records from table

    Hi,

    I am a new member of this forum and also beginner to database. Please let me know ,how to write a query for the below question.

    There is a table with following details.
    Name Total marks
    AAA 300
    BBB 200
    CCC 500
    DDD 150
    EEE 600

    I want this detail to be arranged in descending order of total marks.And a new column should show the order of the result set, ranked from highest to lowest, in the order of Total marks.

    i.e.,
    Output should looklike as follows,
    Name Total marks Rank
    EEE 600 1
    CCC 500 2
    AAA 300 3
    BBB 200 4
    DDD 150 5

    Query:
    Select name,total marks from studenttable orderby 2.
    I want to know ,what to include with this query to get the rank column.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the SQL Reference manual for the OLAP function row_number.

    Andy

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The following will also work:
    Code:
    SELECT a.name, a.total_marks, count(*)
    FROM   student_table AS a INNER JOIN student_table AS b
           ON a.total_marks <= b.total_marks
    GROUP BY a.name, a.total_marks
    HAVING  count(*) < 6
    ORDER BY 2 DESC
    There is nothing specific to DB2 in this solution, though: this is standard SQL (which works with DB2 from at least version 7 on).
    Starting version 8, though, there is indeed a more elegant (standard SQL) solution using OLAP functions.
    Last edited by Peter.Vanroose; 08-23-06 at 04:29.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Aug 2006
    Posts
    3

    Thanks

    Thanks...Peter.Vanroose and Andy......
    Really your answers worked well for me.
    thanks a llot...

  5. #5
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39
    Below would be the query using OLAP function
    db2 "Select Name,TotalMarks ,rank () over(order by totalmarks desc) from table"
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

Posting Permissions

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