Find S_name & S_marks of which student gets 2nd highest marks in table

I have a table name "Student" with two fields 1>S_name ,2>S_marks

I want to find the S_name & S_marks of which student gets the 2nd highest marks in the table "Student." How do I do this?

    Requires Free Membership to View

    Login

The solution is to use the TOP clause on a SELECT statement twice. Let's start by creating the table and inserting some data so we can test the result: 
CREATE TABLE Student (
        S_name varchar(128)
      , S_Marks numeric(5,2)
   )
GO

INSERT INTO Student Values ('Tommy' ,  98.00)
INSERT INTO Student Values ('Eric'  , 100.00)
INSERT INTO Student Values ('Johnny',  97.40)
INSERT INTO Student Values ('Peter' ,  98.50)
GO

Selecting the TOP 2 when the results sorted in descending order gives us a subset of the table rows with the row that we want at the bottom and one other row. Here's how we start: 

SELECT TOP 2 * 
    FROM Student 
    ORDER BY s_Marks desc
GO
(Results)
S_name                         S_Marks 
------------------------------ ------- 
Eric                            100.00 
Peter                            98.50

To get just the second highest scorer, turn this query into a subquery and use the TOP clause again with the sort reversed. Here's the final answer: 

SELECT TOP 1 *
   FROM (
        SELECT TOP 2 * 
           FROM Student 
           ORDER BY S_Marks desc
        ) Top2
   ORDER BY S_Marks asc
GO
(Results)
S_name                         S_Marks 
------------------------------ ------- 
Peter                            98.50

This was first published in April 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top