On Tue 2003-01-14 at 09:22:40 -0500, [EMAIL PROTECTED] wrote:
> > One solution could be something like
> >
> > SELECT id, first, last
> > FROM names
> > WHERE last > '$previouslast' AND first > 'previousfirst'
> > ORDER BY last, first
> > LIMIT 1
[...]
> I don't think it would loop forever, since you search by '>', not
> '>=', so any identical names would be skipped. Either way, though,
> last,first, must be unique.
Correct. Same underlying problem, but wrong description (by me).
Indeed I meant ">=", although my description was not correct for that
either: I meant ">=", but wrote ">" and so some thoughts to both made
it into my post. Thanks for pointing this out.
> A fix to this might be to order by id after this, since id is
> unique
>
> SELECT id, first, last
> FROM names
> WHERE last > 'previouslast' AND first > 'previousfirst' AND
> id>'$previousid'
> ORDER BY last, first, id
> LIMIT 1
>
> Thus sorting through any identical names as well. (How many Bob Smiths can
> there be?)
That was one of the ideas I pondered about originally, but that assumes
that id has a relation to the alphabetical order, which not the case
normally, i.e. assume (sorted by (last,first,id)):
id last first
10 Bar Matt
1 Foo Tom
You'll first get "Bar, Matt (10)" and then try to select the next
record. But the condition id>'$previousid' prevents you from getting
"Foo, Tom (1)". You see?
In principle that is exactly the problem you try to solve below,
extended to id.
> However, this brings out a problem in using AND to combine the elements. by
> saying "WHERE last > '$previouslast' AND first > 'previousfirst'
> ", we're falsly stating that the first name _must_ increase along with the
> last name, where in reality the first name may decrease, so long as the last
> name increases. Follow?
Yes. Another mistake of mine. I shouldn't answer mail when I am in a
hurry. I am sorry about the confusion. The usual condition clause for
such a requirement is of course:
last > 'previouslast' OR ( last='$prevlast' AND first > '$prevfirst' )
This does not take duplicates into account yet and doesn't scale well
with (all versions of) MySQL due to the OR clause.
[...]
> So, whats the best way to rewrite this? Perhaps a Concat?
>
> SELECT id, first, last
> FROM names
> WHERE CONCAT(last,first,id)>CONCAT('$previouslast', '$previousfirst',
> '$previousid')
> ORDER BY last, first, id
> LIMIT 1
>
> I think that would work, feel free to correct me!
Nice idea, but it has several problems. id is usually a number and
alphanumerical comparisons don't work well with them:
"FooTom9" > "FooTom100"
would be true, which is not what we want. Another point is that
CONCAT() on the column part prevents using of indexes. And simply
concatenating strings for sorting could have unwanted side-effects.
Unprobable, but possible. This should work better:
CONCAT(last,'|',first,'|',LPAD(id,11,'0')) >
CONCAT('$prevlast|$prevfirst|',LPAD($previd,11,'0')
It solves at least the most problematic parts reasonbly (but still
cannot use indexes). A similar alternative, which uses the seperate OR
suggested above, could be
CONCAT(last,'|',first) > '$prevlast|$prevfirst') OR
( CONCAT(last,'|',first) = '$prevlast|$prevfirst' AND id>$previd )
In order to use have a chance to use an index I suggest using
something like
SELECT id, last, first
FROM names
WHERE last > '$prevlast' OR
(last = '$prevlast' AND first > '$prevfirst') OR
(last = '$prevlast' AND first = '$prevfirst' AND id > $previd)
ORDER BY last, first, id
LIMIT 1
which is almost what I had in mind originally (using a $skip variable
instead of $previd). One can hope that MySQL notices that it can use
an single index, because all OR parts contain the same column, 'last'.
Well, to get away a bit from the complex discussion: With such a need
it is often easier to do part of the processing in an application.
Therefore start with querying the list of id's sorted as one needs
(supposing the list will not get too big)
SELECT id FROM names ORDER BY last, first, id
and pick the id which comes after $previd in the result set and now
query for the new row by id:
SELECT id, last, first FROM names WHERE id=$pickedid
The same could be archieved with a TEMPORARY table (which doesn't make
sense - the SQL solution above - except if the order involves even
more columns or a more complicated expression):
CREATE TEMPORARY TABLE picknext
(num INT AUTO_INCREMENT PRIMARY KEY, id INT, UNIQUE(id))
SELECT NULL, id FROM names ORDER BY last, first, id
[that is one statement!]
SELECT id, last, first
FROM names
WHERE id IN (
SELECT id FROM picknext WHERE num IN (
SELECT num+1 FROM picknext WHERE id=$previd))
Or without sub-select:
SELECT n.id, n.last, n.first
FROM picknext p
INNER JOIN picknext tmp ON tmp.num = p.num+1
INNER JOIN names n ON n.id = tmp.id
WHERE p.id = $previd
If $previd is not known, the command sequence starts with
SELECT id FROM names WHERE last = $wantedlast AND first = $wantedfirst
Sam, the reason it is quite complex to solve only with SQL is, as I
told you initially, that SQL operates on sets (represented by tables)
and sets have no implicit order and ORDER BY only comes last. If there
are keys or not has to be transparent to the caller and should only
affect performance. So there isn't really a way to say "select the row
next to this name", because "next" implies an order. The solution we
presented is to say, "select all rows which names are 'higher' than
this name, order them and show me only the first one".
Nicholas, thanks again for the corrections.
Regards,
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
