Counting rows when order is ambiguous

[Philip Mak]

Say I have this query:

SELECT *
FROM topics
ORDER BY lastPostTime DESC;

How would I modify it to answer the question "How many rows would be
returned before the row that has topics.id = $x"?

I was thinking of something like this:

$xPostTime = SELECT lastPostTime FROM topics WHERE id = $x;

SELECT COUNT(*)
FROM topics
WHERE lastPostTime > $xPostTime;

but this statement breaks down in the case where there are rows having
lastPostTime = $xPostTime. Would I have to do something un-portable
like this:

SELECT COUNT(*)
FROM topics
WHERE lastPostTime > $xPostTime
OR (lastPostTime = $xPostTime AND id < $x);

or could I do something more elegant that looks like:

SELECT COUNT(*)
FROM topics
ORDER BY lastPostTime DESC
HAVING <<something that's true iff it comes before row with id = $x>>;

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]