On Thu, Feb 26, 2004 at 09:50:39AM -0700, Sasha Pachev wrote:
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
The table has an "id" column ("id" is the primary key) that works like your seq_ord suggestion, so I guess I could have a query like this:
SELECT * FROM topics ORDER BY lastPostTime DESC, id DESC
But then given a certain id = $id, I'm not sure of the best way to count the number of rows that would be returned in the above query before the row with id = $id. Is there any solution that looks like this:
SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC, id DESC HAVING <<not sure what to put here?>>
Or do I have to do this, which feels kludgy:
$postTime = SELECT lastPostTime FROM topics WHERE id = $id;
SELECT COUNT(*) FROM topics WHERE lastPostTime > $postTime OR (lastPostTime = $postTime AND id > $id);
Can you just add id > $id to the where clause?
-- 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]
