Q

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

Development expert Andrew Novick explains how to use a TOP clause on a SELECT statement to query a 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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close