Max Function

  • Thread starter Thread starter svenki15
  • Start date Start date
S

svenki15

I have a table as under

Team Name Position
Team 1 56
Team 1 108
Team 1 65
Team 2 44
Team 2 106
Team 2 34
Team 3 1
Team 3 32
Team 3 65

I want to extract the first two position of each team. Using a grouped by
query and Max in Total rows i get only the max position not the max and the
next highest position. I want my result to look as under

Team Name Position
Team 1 65
Team 1 56
Team 2 34
Team 2 44
Team 3 1
Team 3 32

Please help
 
try something like this:

SELECT [Team Name], Position
FROM [Tablename]
WHERE Position >= dMax("Position","[Tablename]", "[Team Name]='" & [Team
Name] & "' and Position < dMax("Position","[Tablename]", "[Team Name]='"
& [Team Name] & "'"))
ORDER BY [Team Name], Position desc;

this will not work if the top 2 positions for a team have the same value


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
SELECT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

This will return the top 2 positions for each team EXCEPT if there are ties
for the second position. In that case, it will return the ties. In this
particular case you can fix that by using the DISTINCT keyword in the main
query. Using DISTINCT will combine the rows returned if they are identical.
HOWEVER, this leads to another problem. If the tie was for first place, the
team would have only one record showing.

SELECT DISTINCT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

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

If u don't mind what changes do i need to do for bottom two!!

John Spencer (MVP) said:
SELECT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

This will return the top 2 positions for each team EXCEPT if there are ties
for the second position. In that case, it will return the ties. In this
particular case you can fix that by using the DISTINCT keyword in the main
query. Using DISTINCT will combine the rows returned if they are identical.
HOWEVER, this leads to another problem. If the tie was for first place, the
team would have only one record showing.

SELECT DISTINCT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a table as under

Team Name Position
Team 1 56
Team 1 108
Team 1 65
Team 2 44
Team 2 106
Team 2 34
Team 3 1
Team 3 32
Team 3 65

I want to extract the first two position of each team. Using a grouped by
query and Max in Total rows i get only the max position not the max and the
next highest position. I want my result to look as under

Team Name Position
Team 1 65
Team 1 56
Team 2 34
Team 2 44
Team 3 1
Team 3 32

Please help
Click to expand...
Click to expand...
 
Matter Resolved, am highly indebted

Thank You

John Spencer (MVP) said:
SELECT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

This will return the top 2 positions for each team EXCEPT if there are ties
for the second position. In that case, it will return the ties. In this
particular case you can fix that by using the DISTINCT keyword in the main
query. Using DISTINCT will combine the rows returned if they are identical.
HOWEVER, this leads to another problem. If the tie was for first place, the
team would have only one record showing.

SELECT DISTINCT [Team Name], [Position]
FROM [Your Table] as A
WHERE [Position] in
(SELECT TOP 2 B.Position
FROM [Your Table] as B
WHERE B.[Team Name] = A.[Team Name]
ORDER BY B.Position)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a table as under

Team Name Position
Team 1 56
Team 1 108
Team 1 65
Team 2 44
Team 2 106
Team 2 34
Team 3 1
Team 3 32
Team 3 65

I want to extract the first two position of each team. Using a grouped by
query and Max in Total rows i get only the max position not the max and the
next highest position. I want my result to look as under

Team Name Position
Team 1 65
Team 1 56
Team 2 34
Team 2 44
Team 3 1
Team 3 32

Please help
Click to expand...
Click to expand...
 
Back
Top