mySQL question

[mmj]

Hi all,

I have a particular question about my PHP/mySQL database.

I have a database driven site of articles at http://www.mostlyamphigory.com/archives/

Lets say I have an article of id $aid and date $date, and I want to find out what position that article would fall if the articles were sorted by date.

[edit
The table is, of course, indexed by date, so is there a way of finding where this article falls in the index by date?
]

What I could do, is count the number of articles that are newer than this article, and then add one:

"SELECT COUNT(*) FROM articles WHERE date > $date"

But this, I believe, would be quite inefficient. If there were two articles with the same date, they would return the same value. Is there a better way?

Reason:
At the bottom of an article, I'd like to include links to next/previous articles. I need to know where the current article falls to be able to do this.

Thanks in advance

[petesmc]

This may be quite a pointless post, but if you change the date field to a timestamp then you would have to add two articles/posts within one second to have two of the same values.

Peter

[mmj]

It's actually a datetime field so it is accurate to a second.

Duplicates wouldn't really be my main problem But I was wondering if there was a more efficient way of doing it, seeing as that sort of info would be in the index.


To put my original question in a simpler way, let's say I have a table like this:

ID  RATING   MOVIE
_________________________________________
1   6.98     The Emperor's New Clothes
2   8.34     Debbie and the Chocolate Factory
3   2.2      Five Happy Mice
4   4.0      Five Easy Pieces of Chocolate
5   1.78     Star Wars 7: The Five Wars

Let's say the user is viewing a page about "Five Happy Mice" with id of 3 and rating of 2.2

How would I find out where that rating ranks out of every movie in my database? What would be the most efficient way of doing it?