Top 100 and Ties

[Dona]

I want to query the Top 100 scores; however, I want the ties, too. For example, number 100 has a score of 7490. I want to include the additional 5 records with a 7490 score. This would make the query return 105 records. Is there a way to do this without using "Select Top 100 with ties"? How would I make this work with Access 2002 XP?

Thank you.

Dona

 

[john471]

Dona,

In my experience (which does not extend to Access 2002), SELECT TOP inherently does include ties for last place. I suggest you run a simple test to see what you get.

Refer this thread for an example of someone with the opposite problem (wanting tied values for last place excluded from SELECT TOP query).

HTH

Regards

John.

 

[Dona]

The query still returns 100 records. It is not returning the ties.
Here's my query.

SELECT TOP 100 zztab.PERSON_WH_ID, zztab.[Total Academic Score]
FROM zztab;

Any suggestions?
Dona

 

[john471]

You first need to sort on the field to which the TOP statement is to apply.

SELECT TOP 100 zztab.PERSON_WH_ID, zztab.[Total Academic Score]
FROM zztab
ORDER BY zztab.[Total Academic Score] DESC;

(Use "DESC" to get highest scores, or omitt it to get lowest scores)

HTH

Regards

John

 

[Dona]

John,

Here's the query, I originally used.

SELECT TOP 100 [Total Freshmen with Total Academic Score].PERSON_WH_ID, [Total Freshmen with Total Academic Score].[Total Academic Score]
FROM [Total Freshmen with Total Academic Score]
ORDER BY [Total Freshmen with Total Academic Score].[Total Academic Score] DESC;

IT STILL DOESN'T WORK! It should return 107 records with the ties.

Any other suggestions???

Thank you.

Dona

 

[neileg]

What do you want?

Do you want the top 100 scores plus anyone who ties for last place? As John says, a sorted top values query will give you this.

Do you want the top 100 score values and all the students who got this? A nested query will give you this.
Create qryTopScores like this:
SELECT DISTINCT TOP 100 tblScores.Score
FROM tblScores
ORDER BY tblScores.Score DESC;

Then join this query back to the table like this:
SELECT tblScores.ID, tblScores.Name, tblScores.Score
FROM qryTopScores LEFT JOIN tblScores ON qryTopScores.Score = tblScores.Score;

 

[Dona]

TOP 100 and Ties

Thanks Neil it works this way.

Do you know why it doesn't work WITH TIES?

See below:
SELECT TOP 100 WITH TIES [Total Freshmen with Total Academic Score].PERSON_WH_ID, [Total Freshmen with Total Academic Score].[Total Academic Score]
FROM [Total Freshmen with Total Academic Score]
ORDER BY [Total Freshmen with Total Academic Score].[Total Academic Score] DESC;


Thank you

Dona

 

[GabrielR]

Here's how a person that does not know too much about ACCESS (like me) will do it:

1. Design a Query for the top 100
2. Design a query for Last Record of the Top 100 Based on Qry 1
3. Design a query with records that have the same score as query 2
4. Do a Union Query between 1 and 3

There you will have: the Top 100 Plus the scores that are equal to the 100th Place.

 

[neileg]

Do you know why it doesn't work WITH TIES?

I'm no SQL expert, but I've never seen the WITH TIES qualifier. You still haven't explained what it is you want to return from the query...

 

[Dona]

Hi

Brian from the Microsoft.public.access.queries was able to resolve the problem. Here is the solution

SELECT
[Total Freshmen with Total Academic Score].PERSON_WH_ID,
[Total Freshmen with Total Academic Score].[Total Academic Score]
FROM
[Total Freshmen with Total Academic Score]
WHERE
[Total Freshmen with Total Academic Score].[Total Academic Score]
IN
(SELECT TOP 100
SELECT
[Total Freshmen with Total Academic Score].PERSON_WH_ID,
[Total Freshmen with Total Academic Score].[Total Academic Score]
FROM
[Total Freshmen with Total Academic Score]
WHERE
[Total Freshmen with Total Academic Score].[Total Academic Score]
IN
(SELECT TOP 100
[Self].[Total Academic Score]
FROM
[Total Freshmen with Total Academic Score] AS [Self]
ORDER BY
[Self].[Total Academic Score] DESC)
[Total Academic Score]
FROM
[Total Freshmen with Total Academic Score] AS [Self]
ORDER BY
[Self].[Total Academic Score] DESC)