Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: fetching second maximum value, third maximum value...etc etc

    Hi guys,
    I have a doubt. I want to fetch second maximum value of a particular column. For example, i Have a table named by "student". I have two fields, name and marks.

    name marks
    ---------- --------------
    Ram 67
    ramesh 70
    rajesh 56
    anil 80
    shikahr 87
    zozo 78

    Now , i want to fetch second maximum marks which is 80, third maximum marks which is 78 and so on.

    I want a query t0 fetch the marks indivudually.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT name, marks FROM student ORDER BY marks DESC
    as you retrieve the rows from this query, the second highest mark is in the second row, the third highest mark is in the third row, and so on

    i mean, come on, how hard can it be

    surely you have failed to communicate the entire gist of your homework assignment here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile error!!!! i know thsi query, sir :)

    i said, i want it individually.

    Th ethings is that i want to move tehse values intio another table. structure of another table is


    name marks1 marks2 marks3 makrs4 makrs5
    ------ --------- -------- ---------- --------- ---------


    marks contains the maximum value, amrks 2 next maximum value, marks3 3rd maximum value and so on....

    i am getting this desired result by this query:-

    select max(marks) from student where marks < (select max(marks) from student where marks < (select max(marks) from student where marks < (select max(marks) from student)))


    this is for 3rd maximum value...

    but my problem is that here marks is in integer datatype and i have a column containing data like 3.40-5.00/ 455-67. it;s difficult to find out the maximum value also.

    Thanks

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    sunny 007, I would look into the OLAP function ROW_NUMBER(). This assumes you can sort MARKS to get them in order. The function will assign a sequential number to the rows and this sequential number might be used to do what you want.

Posting Permissions

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