Query to retrieve top 2 of the Name & marks

[Kumaresan]

Imput Table example Eg:
Table1
Id Name Marks
1 Test 50
2 Test 75
3 Test 60
4 Test1 85
5 Test1 95
6 Test1 90

I want to retrieve the top 2 marks(Desc order) based on name
Output as Eg:
Id Name Marks
1 Test 75
2 Test 60
3 Test 1 95
4 Test1 90


Can somebody help me to send query for this?

 

[Duane Hookom]

Did you really want different Id values in your output? I assume that was one
of your typos.

Try this SQL:
SELECT Imput.*
FROM Imput
WHERE Imput.ID In (SELECT TOP 2 ID FROM Imput I WHERE I.Name = Imput.Name
ORDER BY Marks DESC);

 

[Allen Browne]

See:
http://allenbrowne.com/subquery-01.html#TopN
The article shows how to use a subquery to retrieve the TOP 2 records for
each test.

 

[John Spencer MVP]

One method uses a correlated sub-query to identify the records you want to show.

SELECT Table1.*
FROM Table1
WHERE Marks in
(SELECT TOP 2 Marks
FROM Table1 as Temp
WHERE Temp.Name = Table1.Name
ORDER BY Marks DESC)

An alternative is to use a ranking query to get the Top 2. This method can be
slightly better in that you can set the number of records to be returned by
changing the limit in the Having clause using a parameter. You cannot change
Top 2 using a parameter but have to directly edit the query.

The first method allows you to edit records, the second does not. HOWever if
you wish there is a way to make the second return records that can be edited -
use it to return just the ID field and use it in a where clause.

SELECT A.ID, A.Name, A.Marks
FROM Table1 as A LEFT JOIN Table1 as B
ON A.Name = B.Name
AND A.Marks < B.Marks
GROUP BY A.ID, A.Name, A.Marks
HAVING Count(B.Marks)< 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

***if*** someone uses MS SQL Server 2005 or later, the n in TOP n can be an
argument as long as the argument name is between [ ]:


SELECT TOP [@n] * FROM ...


could accept the parameter @n. Unfortunately that is (at this moment) not
available to Jet.


Vanderghast, Access MVP

 

[Kumaresan]

Thanx a lot John. This is what i wanted. It working.