Hi.
On Tue 2002-11-12 at 13:43:09 -0700, [EMAIL PROTECTED] wrote:
> I can't seem to come up with a solution to the following SQL query, and have
> a project dependent on a solution. I'd rather not load the whole database
> and then parse it in the code.
>
> With the following Database structure:
> Field0 Integer Autonumber
> Field1 Integer Unique
> Field2 Integer not unique
>
> where the starting record would be selected by Field1 (an indexed field of
> unique values).
>
> The ending record required would be the first succeeding record where the
> value of Field2 is equal to the value of Field2 in the 'starting' record.
"succeeding record" implies an order. SQL tables have not implicit
order. Do you mean when ordered by Field0?
> Field2 is NOT unique and not ordered.
> The result would be ordered by Field0 (the autonumber field)
Presuming you mean "next" when ordered by Field0, I see no easy way to
do it in one query. But you can do
SELECT Field0, Field2
FROM yourtable
WHERE Field1 == "somefoo"
Remember Field0 as startf0 and Field2 as startf2.
SELECT Field0
FROM yourtable
WHERE Field0 > "startf0" AND
Field2 == "startf2"
ORDER BY Field0
LIMIT 1
Remember Field0 as stopf0.
SELECT *
FROM yourtable
WHERE Field0 BETWEEN "startf0" AND "endf0"
ORDER BY Field0
(Where "startf0" does not mean that you shall write it as string. I
just wanted to differenciate it from column names.)
The reason it is not possible to write that as one query (in MySQL) is
due to the ORDER BY ... LIMIT 1 trick. You could omit the first query,
by using a self-join on each of the following two, but speed-wise,
that is probably not an improvement.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
