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 last published in April 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close