Query to retrieve top 2 of the Name & marks

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?

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);

--
Duane Hookom
Microsoft Access MVP


"Kumaresan" wrote:

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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kumaresan" wrote in message
news
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?

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

Kumaresan wrote:
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?

***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



"John Spencer MVP" wrote in message
...
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

Kumaresan wrote:
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?

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

"John Spencer MVP" wrote:

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

Kumaresan wrote:
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?