Results 1 to 8 of 8

Thread: Top rows

  1. #1
    Join Date
    Oct 2013
    Posts
    7

    Unanswered: Top rows

    without using OLAP functions like RnK FUNCTION

    How can I get the top 3 Rows over Grades like
    I have the following table T1

    Std_Name | Grade
    ************************ ALI | 60

    San | 55

    SARA | 50

    JOER | 50

    MANDA | 50

    AEROP| 25

    NOAM | 24

    How can I got top 3 students ordered by Highest grade

    THE RESULT SHOULD BE LIKE

    Std_Name | Grade
    ************************

    1- ALI | 60

    2- San | 55

    3- SARA | 50

    3- JOER | 50

    3- MANDA | 50

    -------------------
    THANKS

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I like to start new problem afer finished existing problem.
    http://www.dbforums.com/db2/1697857-avg-weeks.html

  3. #3
    Join Date
    Oct 2013
    Posts
    7
    please anyone help me

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Nice homework.
    Look at FETCH FIRST n ROWS ONLY
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "FETCH FIRST n ROWS ONLY" may be not complete.

    Because, there were some students in 3rd grade in the example,
    and the requiremens might be to include all students in 3rd grade.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I was thinking of something like:

    SELECT *
    FROM Grades
    WHERE Grade IN
    (
    SELECT DISTINCT Grade
    FROM Grades
    ORDER BY Grade DESC
    FETCH FIRST 3 ROWS ONLY
    )
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Don't forget to order your results.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    michael0,

    If multiple student were in first grade or second grade,
    what result do you want?

    For example

    Case 1:
    Code:
    Std_Name Grade
    -------- -----
    ALI      60
    San      60
    SARA     60
    JOER     60
    MANDA    50
    AEROP    25
    NOAM     24
    Case 2:
    Code:
    Std_Name Grade
    -------- -----
    ALI      60
    San      55
    SARA     55
    JOER     50
    MANDA    50
    AEROP    50
    NOAM     24

    If the required results were the followings,
    aflorin27's query might be good.

    Case 1:
    Code:
    Std_Name Grade
    -------- -----
    ALI      60
    San      60
    SARA     60
    JOER     60
    MANDA    50
    AEROP    25
    Case 2:
    Code:
    Std_Name Grade
    -------- -----
    ALI      60
    San      55
    SARA     55
    JOER     50
    MANDA    50
    AEROP    50

Tags for this Thread

Posting Permissions

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