Re: Counting rows when order is ambiguous

2004-02-26 Thread Sasha Pachev 
Philip Mak wrote:
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 <>;
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]

Re: Counting rows when order is ambiguous

2004-02-26 Thread Philip Mak 
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 <>

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]

Re: Counting rows when order is ambiguous

2004-02-26 Thread Sasha Pachev 
Philip Mak wrote:
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 <>
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]

Re: Counting rows when order is ambiguous

2004-02-26 Thread Philip Mak 
On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote:
> >SELECT COUNT(*)
> >FROM topics
> >WHERE lastPostTime > $postTime
> >OR (lastPostTime = $postTime AND id > $id);
> 
> Can you just add id > $id to the where clause?

No, that won't work because id is only used to disambiguate the order
of two rows that have the same lastPostTime.

(This is a forum software where topics.lastPostTime indicates the last
time a topic was posted in. This may be in a different order than the
topics were originally created.)

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

Re: Counting rows when order is ambiguous

2004-02-26 Thread Sasha Pachev 
Philip Mak wrote:
On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote:

SELECT COUNT(*)

FROM topics

WHERE lastPostTime > $postTime
OR (lastPostTime = $postTime AND id > $id);
Can you just add id > $id to the where clause?


No, that won't work because id is only used to disambiguate the order
of two rows that have the same lastPostTime.
(This is a forum software where topics.lastPostTime indicates the last
time a topic was posted in. This may be in a different order than the
topics were originally created.)
Then I would use the last query you proposed except rewrite it to use UNION 
instead of OR to ensure a better use of keys.

--
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]

Counting rows when order is ambiguous

2004-02-25 Thread 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 <>;

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