Hi.
On Tue 2003-01-14 at 06:02:10 +0000, [EMAIL PROTECTED] wrote:
> > SELECT id,first,last FROM names ORDER BY id LIMIT 2,1;
> >
> > Work? Limits to one result, start at second offset. (I may have the 2,1 in
> > the wrong order though)
>
> Ok my gut would say that this would not give the result I want
> unless the id's are sorted by last name, and given inserts and such
> I can see that would not be the case. But I sense the glimmer of an
> approach in this query...
The problem is that you want to retrieve a row depended on the order
of name and that tables in SQL are (unsorted) sets. ORDER BY applies
only after the result set has been determined and cannot be used
directly to influence what the result will be (only how it looks
like).
One solution could be something like
SELECT id, first, last
FROM names
WHERE last > '$previouslast' AND first > 'previousfirst'
ORDER BY last, first
LIMIT 1
That is: Take all results that "come after" the previous name, sort
them and then take only the first.
The problem with it is that it only works fine as long as (last,first)
is unique. If not, you will end up looping on the same (last,first)
pair forever.
One way to solve is to pass a 'skip' value, which tells you how often
you already encountered this (last,first) pair and skip that many
rows. You would start with skip=0 and reset skip to 1 whenever you
encounter a different (last,first) pair. In this case you would use
something like
... LIMIT $skip, 1
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
