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.

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

 

[Rick Brandt]

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?

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

 

[Wayne Morgan]

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.

 

[Dona]

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

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

.

 

[Rick Brandt]

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

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.

 

[Wayne Morgan]

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.

 

[Guest]

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

--
Wayne Morgan
MS Access MVP

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

.

 

[Wayne Morgan]

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.

 

[Dona]

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
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.a spx.

--
Wayne Morgan
MS Access MVP

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?


.

 

[John Spencer (MVP)]

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,

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
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.a spx.

--
Wayne Morgan
MS Access MVP

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?


.

 

[Tim Ferguson]

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

 

[Guest]

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

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
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.a

spx.

--
Wayne Morgan
MS Access MVP


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?


.

.

 

[Tim Ferguson]

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)

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