Determining Ranks In An SQL Database

[Anon]

There is probably a really simple way of accomplishing this, but none that I can think of...

Say, for instance, I have an SQL databse of files. One of the fields is "downloads." Is there any way of determining the file's "rank" (like "This file is ranked 23 of 84 by downloads") with some sort of SQL statement?

Thanks in advance.

[smarlowe]

assuming that the database contains a field something like "no_of_downloads" you could do it like:

select * from table order by no_of_downloads DESC limit 5

or something like that.

[Anon]

Hmm..maybe I wasn't clear enough..

Say I have a page displaying the file information already (description, author, downloads) how can I THEN display the rank without displaying any more results? I hope I made sense there.

Thanks again for the help, Scott.

[smarlowe]

Build the page all at once WITH the ranking information at the same time is how I'd do it.

Otherwise you could launch a page with target=_blank from javascript that told the user what the ranks of the files were.

[amcgrath]

Andrew, you might wanna also determine what criteria you plan on using to actually "rank" a download... if it is user submitted ranks, then store em in a table add em up, dvide etc to get your rank....

[Anon]

It would take 2 queries. The first would get all your information and the second would do something like

SELECT COUNT(fileid) AS rank
FROM files
WHERE num_of_downloads > {number of downloads for your file}