To get a new column while selecting records from table

hvijayakumar · #1 (**permalink**) 08-22-06, 08:56

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.

ARWinner · #2 (**permalink**) 08-22-06, 11:40

Look in the SQL Reference manual for the OLAP function row_number.

Andy

Peter.Vanroose · #3 (**permalink**) 08-23-06, 01:06

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.