Top 20 - Again

[kovan]

hmm
after suggestions
i used the following

SQL = "SELECT TOP 20 ArtistName, SongName, SongID, Location, SongTime, SongHits FROM Songs, Albums, Artists WHERE (Artists.ArtistID = Albums.ArtistID) AND (Albums.AlbumID = Songs.AlbumID) AND Songs.SongHits > 0 ORDER BY Songs.SongHits DESC"

but it doesnt get only first 20
it gets all that have Songs.SongHits > 0
so it returns all
grrr
any suggestion

[paulw]

Are you sure that the Ties aren't the problem?

Cheers,

P.

[kovan]

pretty sure

i was told it cannot be done in access
then i was told the only way to do it in access is to use the command object (grrr) dont know how to use it thou

i just need to get me sql server

[Gaffer]

You can't use ORDER BY and TOP in the same SQL-statement. Remove the order by and try again...

(here's hoping!)

[kovan]

but how do you tell the query that you want the 20 highest values for SongHits?

[paulw]

Gaffer,

I don't think that is not right.

TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate doesn't choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause.

Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.TOP doesn't affect whether or not the query is updatable.

That is from the Access Help file and it does work, as far as I know.

I have used TOP successfully in Access. Who told you it won't work? What environment are you using?


I would also try using the JOIN predicates instead of the WHERE a = b form.

Cheers,

P.

[Edited by paulw on 11-20-2000 at 09:37 AM]

[kovan]

using asp, ado and access2k
stupid thing doesnt work for me

grr
what do i do?

[paulw]

In the Bible it says:

Use thee not the ADO...

but it also says,

TOP always worketh, unless thou art obtuse...

Is that a corruption or an inconsistency?

P

Sorry, k, couldn't resist that. Seriously, it should work. Have you tried re-writing with JOIN?

Send me the app (if it is not too big) and I'll try it at home.

P.

[Gaffer]

lol

Nightmare day, every decision I've made has been wrong. Going home now!

The only person that told me that untruth was my own head. I have tried a number fields in the ORDER BY statement to varying degrees of success. I think the order by statement is the key here. How about building a query on top of a sorted query? Or a table? (crude by for testing may provide a clue)

Best not raise this faux pas on Wednesday, Paul….

[kovan]

paulw
you know i admire you a LOT
you have a sense of humor

i thought you said i wasnt obtuse? hehe
not that i am of course
but thanks for the help

i have the asp pages here on my labtop
i can send them to ya if you like
actually i will send them either you like it or not

remember, bible encourages to help others
so did Jesus (pbhu)

but on the serious side

thanks for the offer
i will send them when i can