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>>;
Philip:
If I understood the problem correctly, the answer to it is actually undefined. If you order by lastPostTime, the records with the same lastPostTime value can be returned in any order.
I guess to accomplish your goal you could add a column seq_ord int not null to keep track of the record order according to your expectations, and then order by lastPostTime,seq_ord
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
