Top 100 and Ties

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dona - 13 Jan 2005 18:50 GMT

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.

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;

Is there a way to do this using "Select Top 100 with
ties"? How would I make this work with Access 2002 XP?
Suggestions?

Thank you in advance.

Dona

Reply to this Message

Rick Brandt - 13 Jan 2005 19:17 GMT

> I want to query the Top 100 scores; however, I want the
> ties, too. For example, number 100 has a score of 7490. I
[quoted text clipped - 11 lines]
> ties"? How would I make this work with Access 2002 XP?
> Suggestions?

My understanding is that Access (by default) does include ties. Are you
working against a SQL Server database?

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Wayne Morgan - 13 Jan 2005 19:23 GMT

Top will return the ties in the last position, giving more than the number
of records asked for. It will work on the first sorted field.

However, it won't return this if the ties are in earlier positions. It will
only return more records than asked for if the tie is in the final record.

Example:
Asking for Top 3, the * record would be the last one returned.

1
1
2
2
2*
3
3
3+
4
5

1
2
3*
4
5
6

1
1
1*
2
2
3
3
3+

To get the Top Unique values, regardless of the number of records, try
something like this.

SELECT Table2.Field1, Table2.Field2, Table2.Field3, Table2.Text3
FROM Table2
WHERE (((Table2.Field1) In (SELECT DISTINCT TOP 3 Table2.Field1 FROM Table2
ORDER BY Table2.Field1;)))
ORDER BY Table2.Field1;

The + in the listings above indicate the last record returned by the above
query.

Signature

Wayne Morgan
MS Access MVP

>I want to query the Top 100 scores; however, I want the
> ties, too. For example, number 100 has a score of 7490. I
[quoted text clipped - 15 lines]
>
> Dona

Reply to this Message

Dona - 13 Jan 2005 20:22 GMT

Wayne,

I want the query to return 100 top records plus the ties.
I used the following query.

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

Any suggestions on how to pick up the ties?

Thanks.

Dona

>-----Original Message-----
>Top will return the ties in the last position, giving more than the number
[quoted text clipped - 66 lines]
>
>.

Reply to this Message

Rick Brandt - 13 Jan 2005 21:31 GMT

> Wayne,
>
[quoted text clipped - 6 lines]
> FROM [Total Freshmen with Total Academic Score]
> ORDER BY [Total Freshmen with Total Academic Score

Define the problem spec.

Let's use an example with TOP 5. Given the scores...

100
100
90
90
80
80
80
70
70
60
50

...what do you want to see in the output? A normal TOP 5 query in Access
Jet will return 7 records...

100
100
90
90
80
80
80

...which represent the top 5 values with two additional since they are tied
at 80. If you want the TOP 5 "Scores" and everyone who has them then you
want...

100
100
90
90
80
80
80
70
70
60

...since the Top 5 unique scores are 100, 90, 80, 70, and 60. That would
either require multiple queries or the use of sub-queries. The former is
the easiest. You would first run a DISTINCT query to get all unique scores.
Feed that into a second query that gives you the TOP 5 of the first one, and
then use that query joined to the original table to retrieve all students
who had those scores.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Wayne Morgan - 13 Jan 2005 21:39 GMT

I hope what you have here is typed and not copied and pasted from SQL view.
Try a space after 100 and you're missing the closing bracket and field name
in the ORDER BY clause.

In your original post, the sort order for [Total Academic Score] was DESC.
This will sort in decreasing order. If you open the query without the "TOP
100" in it, start at the top and count down 100 records. These are the
records the TOP query will return. If the 100th record is tied (in just the
[Total Academic Score] field) with records after it, the TOP query will
return those tied records as well.

This is how Access will handle the query if you are dealing with strictly an
Access (Jet) database. If you are dealing with something else also, let us
know.

Signature

Wayne Morgan
MS Access MVP

> Wayne,
>
[quoted text clipped - 6 lines]
> FROM [Total Freshmen with Total Academic Score]
> ORDER BY [Total Freshmen with Total Academic Score

Reply to this Message

- 13 Jan 2005 21:55 GMT

SELECT TOP 100 [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;
This is my code.... it Returns 100 Records and does not
continue to return the other ties. I understand what you
are saying but it just not seem to work in my query. Could
the fact that im using an Access 2000 file mean anything?

>-----Original Message-----
>I hope what you have here is typed and not copied and pasted from SQL view.
[quoted text clipped - 24 lines]
>
>.

Reply to this Message

Wayne Morgan - 13 Jan 2005 22:46 GMT

Access 2000 is Jet 4, which is what I'm using. You may want to make sure
that you have the current versions of Jet and Office service packs
installed. Also, make sure that you haven't set the maximum number of
records returned to 100. If so, it will truncate at 100, regardless of the
TOP function. This will only affect an ODBC connection, not data pulled from
an mdb file. Again, where are the tables located? Are they in this or
another mdb file or are they linked tables from another type of database
file?

For the updates, you can try http://windowsupdate.microsoft.com,
http://office.microsoft.com/en-us/officeupdate/default.aspx, and
http://msdn.microsoft.com/data/downloads/updates/default.aspx.

Signature

Wayne Morgan
MS Access MVP

> SELECT TOP 100 [Total Freshmen with Total Academic Score].
> [Total Academic Score]
[quoted text clipped - 5 lines]
> are saying but it just not seem to work in my query. Could
> the fact that im using an Access 2000 file mean anything?

Reply to this Message

Dona - 14 Jan 2005 00:22 GMT

Wayne,

We just did the update. It still doesn't work. I pulled
the original tables from an ODBC connection, but made new
tables in Access.mdb. The tables are located in an mdb
file. They are not linked. I even made a new mdb file and
imported an abbreviated table. It still does not work.
I guess I'll try again on Tuesday.
Have a good weekend.

Thanks.

Dona

>-----Original Message-----
>Access 2000 is Jet 4, which is what I'm using. You may want to make sure
[quoted text clipped - 19 lines]
>> are saying but it just not seem to work in my query. Could
>> the fact that im using an Access 2000 file mean

anything?

Reply to this Message

John Spencer (MVP) - 14 Jan 2005 01:18 GMT

The only thing I can see that has not been mentioned is the possibility that the
scores are not really tied.

Try forcing the score using the CLng or CCur functions and see if that changes
your result.

SELECT TOP 100 T.PERSON_WH_ID,
T.[Total Academic Score]
FROM [Total Freshmen with Total Academic Score] As T
WHERE T.[Total Academic Score] Is Not Null
ORDER BY CLng(T.[Total Academic Score]) DESC;

> Wayne,
>
[quoted text clipped - 53 lines]
> >
> >.

Reply to this Message

- 18 Jan 2005 21:37 GMT

Your example did not work. Someone from the
Microsoft.public.access.queries board was able to help.
The solution was:
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)

>-----Original Message-----
>The only thing I can see that has not been mentioned is the possibility that the
[quoted text clipped - 69 lines]
>> >
>.

Reply to this Message

Tim Ferguson - 19 Jan 2005 17:03 GMT

<anonymous@discussions.microsoft.com> wrote in news:165201c4fda5$f5f08bb0
$a501280a@phx.gbl:

> WHERE
> [Total Freshmen with Total Academic Score].[Total Academic
[quoted text clipped - 6 lines]
> ORDER BY
> [Self].[Total Academic Score] DESC)

I don't believe that this will actually give you what you want. Consider
the table:

ID SomeNumber
1 1
2 1
3 2
4 3
5 4
etc etc

and the query

SELECT TOP 4 SomeNumber
FROM MyTable
ORDER BY SomeNumber

then you get the result set 1,1,2,3 -- i.e. the top four records, not the
top four values. I seem to remember from an earlier post, you wanted all
four values, not four records.

This does work, in Access at least:

SELECT TOP 4 SomeNumber
FROM
( SELECT DISTINCT SomeNumber
FROM TestNumbers
)
ORDER BY SomeNumber

Perhaps your WHERE clause should be:

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

(The aliases o, m, and i refer to outer, middle, and inner), which returns
the personIDs of the people with the highest distinct 100 scores.

Hope that helps

Tim F

Reply to this Message

Tim Ferguson - 14 Jan 2005 18:25 GMT

> However, it won't return this if the ties are in earlier positions. It
> will only return more records than asked for if the tie is in the
> final record.

Another approach: you want the set of records that correspond to the set of
max (x) values... As far as I can tell, the difficulty is getting the
values flattened before being counted. Try something like (although the
internal subquery might have be to compiled into a querydef of its own) -

select f.id, f.fullname from freshmen as f
where f.score >= all
( select top 100 score from
( select unique s.score from freshmen as s)
order by s.score
)
order by f.score desc;

Hope that helps

Tim F

Reply to this Message