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);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
