Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: