Re: select the next name
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 name
Re: select the next name
It seems you want something like select * from table limit 1,1 for the first query and then... select * from table limit 2,1 You might also want to add "order by" as well to assure the proper order each query. HTH, Ron [EMAIL PROTECTED] wrote: > Now Ben did simplify the problem correctly as well I think: > - Run program, fetches a name. Processes and exits. > - Run program again, same SQL but gets the NEXT name. Processes and exits. > > Thanks, >Sam D - 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
RE: select the next name
> I thought about answering your original post except that I couldn't figure > out what you want. And after reading this, I'm even more confused. What > does it mean for id's to be sorted by last name? Ok let me see if I can state thre problem better (though I think that I am begining to understand the magnatude of the task a little better from the answers by Adam and Ben), the problem may be that I don't have a real example only one that came to me as I was reading the section on indexes. Given: a table a table "names" with columns id- integer,primary key, auto_increment first - varchar(20) last - varchar(20) the entries have not been made in alphabetical order. But the table is indexed by last and first Problem: I know a row in the table, (I have an id,first and last). I guess what I would want to know is the next row if the table were sorted by last,first. What I was wondering was if mysql had a way to use the index for this, kind of like, if I know the primary key for a row is there anyway to get the primary key that is next in the index (since the index is kept sorted)? Or am I conceptualizing the index incorectly? SInce if I had the primary key writing a select statement is beginner stuff (and I am a beginner) Now Ben did simplify the problem correctly as well I think: - Run program, fetches a name. Processes and exits. - Run program again, same SQL but gets the NEXT name. Processes and exits. Thanks, Sam D - 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
Re: select the next name
> 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.
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. 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?)
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? Here:
Elliott, Nicholas
Smith, Bob
After Finding Elliott, Nicholas, the Query would _not_ find Bob Smith, even
though 'Smith'>'Elliott', because 'Bob' is not greater that 'Nicholas'.
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!
Nicholas Elliott
-
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
Re: select the next name
Hi. On Tue 2003-01-14 at 06:02:10 +, [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
RE: select the next name
> 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... You're right, I don't know why I thought you wanted to sort by ID. Guess I need more coffee. I think I understand what you're trying to do: - Run program, fetches a name. Processes and exits. - Run program again, same SQL but gets the NEXT name. Processes and exits. LIMIT would work, but you would need to pass the current offset to the next instance of your code. Of course, that counter would need to be reset should the table data change at all and it would be an ugly hack regardless. Forget about it. What you're really looking for is a method to maintain state between DB connections. Unfortunately, no RDBMS that I can think of supports this internally. I think the best solution for you is to add another column to the table and use that for a "skip" flag. Your SQL is this: SELECT id,first,last FROM name WHERE skip=0 ORDER BY last LIMIT 1; UPDATE name SET skip=1 where id=; If your skip flag defaults to 0 (false) then new entries will not require a reset. Once you've exhausted the table (everyone has a skip of 1 (true)) you can set them all to 0 and start over again at the top. I don't think this would be very elegant but it would do the job. A trigger would be handy here. Adam Erickson - 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
RE: select the next name
At 6:02 + 1/14/03, [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... I thought about answering your original post except that I couldn't figure out what you want. And after reading this, I'm even more confused. What does it mean for id's to be sorted by last name? Sam D - 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
RE: select the next name
> 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... Sam D - 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
RE: select the next name
Would: 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) > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 13, 2003 10:20 PM > To: [EMAIL PROTECTED] > Subject: select the next name > > > To begin with I am only about 3/4 of the pay through paul d's > great book so bear with me. Say I have a table (names) with > columns (id, first, last) and I want the next name when ordered > by last, first in the table, say given a particular id. So how > would I frame the query. Something like > > select id,last, first from names where next id from index of last,first > > ??? > > Thanks > > Sam D > > > - > 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 - 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
