Top 100 Records 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.
Here's the 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].[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? SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona

 

[Brian Camire]

This is how TOP works.

In other words, you'll get the additional 5 records.

 

[Dona]

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona

-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

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.
Here's the 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]. [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? SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona

.

 

[Guest]

for some reason it does not work on my machine do you know
of an alternative or a fix for my machine. Im running
Access 2002 on Win 2k

-----Original Message-----
The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona

-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

7490.

I

want to include the additional 5 records with a 7490
score. This would make the query return 105 records.
Here's the 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]. [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? SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona

.

.

 

[Rick Brandt]

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

You might be assuming the ties should be evaluatred on the score only. In
fact Access considers ALL of the fields in the query. So if your query
returns the rows...

John Doe 100
Mary Smith 100

....Access does not consider this a tie. "J" occurs first in the alphabet so
John gets in and Mary gets the boot.

If you want the ties based only on the score then the score must be the only
field in the query's output.

 

[Brian Camire]

Only the field(s) in the ORDER BY are considered.

For example, if your table contains:

Name,Value
John Doe, 100
Mary Smith, 100

then

SELECT TOP 1
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Value]

will return both records.

 

[Brian Camire]

What datatype is "Academic Score"? Is it possible that the values have a
fractional component that's not being displayed (for example, a value is
7490.1, but is being displayed as 7490)? If so, then there may actually be
no ties.

Are you working with an Access database (in an .mdb file) or an MSDE/SQL
Server one (through a .adp file)? Unlike Access (Jet), the default behavior
of SELECT TOP in MSDE/SQL is to exclude ties.

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona

-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

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.
Here's the 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]. [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? SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona

.

 

[Dona]

Brian,
The value is a whole number. The field is from a table
and is a .mdb file.

This is ACCESS 2002. One Access Reference book says "Find
Top (n) records in a query only displays the first n in th
dynaset. I want to pick up the ties using only one query.

Dona

-----Original Message-----
What datatype is "Academic Score"? Is it possible that the values have a
fractional component that's not being displayed (for example, a value is
7490.1, but is being displayed as 7490)? If so, then there may actually be
no ties.

Are you working with an Access database (in an .mdb file) or an MSDE/SQL
Server one (through a .adp file)? Unlike Access (Jet), the default behavior
of SELECT TOP in MSDE/SQL is to exclude ties.

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona

-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

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.
Here's the 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]. [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?
SQL

doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona



.

.

 

[Brian Camire]

The documented behavior of SELECT TOP is such that it may return more than
the specified number of records if there are ties for the "last" spot. See
"...the TOP predicate does not choose between equal values..." at:

http://office.microsoft.com/en-us/assistance/HP010322051033.aspx

In any case, you might try reformulating your query as follows:

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)

but you shouldn't have to.

Brian,
The value is a whole number. The field is from a table
and is a .mdb file.

This is ACCESS 2002. One Access Reference book says "Find
Top (n) records in a query only displays the first n in th
dynaset. I want to pick up the ties using only one query.

Dona

-----Original Message-----
What datatype is "Academic Score"? Is it possible that the values have a
fractional component that's not being displayed (for example, a value is
7490.1, but is being displayed as 7490)? If so, then there may actually be
no ties.

Are you working with an Access database (in an .mdb file) or an MSDE/SQL
Server one (through a .adp file)? Unlike Access (Jet), the default behavior
of SELECT TOP in MSDE/SQL is to exclude ties.

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona
-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

message
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.
Here's the 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].
[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?
SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona



.

.

 

[Rick Brandt]

Only the field(s) in the ORDER BY are considered.

For example, if your table contains:

Name,Value
John Doe, 100
Mary Smith, 100

then

SELECT TOP 1
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Value]

will return both records.

You're correct. My original test was with a small table and no ORDER BY clause
at all. Apparently that is treated the same as having all of the fields in the
ORDER BY.

 

[Dona]

Thank you very much the alternative format worked great!

-----Original Message-----
The documented behavior of SELECT TOP is such that it may return more than
the specified number of records if there are ties for the "last" spot. See
"...the TOP predicate does not choose between equal values..." at:

http://office.microsoft.com/en- us/assistance/HP010322051033.aspx

In any case, you might try reformulating your query as follows:

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)

but you shouldn't have to.

Brian,
The value is a whole number. The field is from a table
and is a .mdb file.

This is ACCESS 2002. One Access Reference book says "Find
Top (n) records in a query only displays the first n in th
dynaset. I want to pick up the ties using only one query.

Dona

-----Original Message-----
What datatype is "Academic Score"? Is it possible that the values have a
fractional component that's not being displayed (for example, a value is
7490.1, but is being displayed as 7490)? If so, then there may actually be
no ties.

Are you working with an Access database (in an .mdb

file)
or an MSDE/SQL

Server one (through a .adp file)? Unlike Access (Jet), the default behavior
of SELECT TOP in MSDE/SQL is to exclude ties.

The query only returns the 100. I want it to return the
additional 5 records. It DOES NOT.

Any suggestions.

Thanks. Dona
-----Original Message-----
This is how TOP works.

In other words, you'll get the additional 5 records.

message
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.
Here's the 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].
[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?
SQL
doesn't seem to recognize "WITH TIES"

Your suggestions are appreciated.

Thank you in advance.

Dona



.



.

.