Sorry I have not been careful enough. Following is a very concrete,
worked example -- so I think I have finally gotten the bugs out. After
the example I resume with unanswered questions.
Remember I did not say each integer appears only once, and consider this
dataset:
create table t (s char(
Mike,
>Oops, I did not read your original query closely enough.
>You actually meant to group by S, not I, right?
No, it's a query for next i values with matching s values, so it groups
by i.
>I can get S, I, and J with
>SELECT a.s, a.i, MIN(b.i) AS j
>FROM t AS a
>JOIN t AS b ON b.i > a.i
Oops, I did not read your original query closely enough. You actually
meant to group by S, not I, right? I can get S, I, and J with
SELECT a.s, a.i, MIN(b.i) AS j
FROM t AS a
JOIN t AS b ON b.i > a.i AND a.s = b.s
GROUP BY a.s
Right?
My integers are not unique; a given integer can be pair
>I do not follow why you suggested a join to get the associated S,
>that can be done in the original query (and I did NOT say a given
>integer I is associated with only one string S):
A Group By query returns arbitrary values for a column which (i) does
not Group By, (ii) does not aggregate, and
Ah, yes, the MIN should be very helpful. Can I expect that ordering the
storage by (S, I) or having an (S, I) index will make that MIN take O(1)
time, for both MyISAM and InnoDB?
I do not follow why you suggested a join to get the associated S, that can
be done in the original query (and I did
Mike,
Yes, for each (S, I) pair the goal is to efficiently find the next largest
integer associated with S in T. For the highest integer I associated with
S in T, there is no next larger.
Here's a more efficient query for the next i values with matching s values:
SELECT a.i, MIN(b.i) AS j
F
Yes, for each (S, I) pair the goal is to efficiently find the next largest
integer associated with S in T. For the highest integer I associated with
S in T, there is no next larger.
Thanks,
Mike Spreitzer
Peter Brawley
06/20/09 08:56 AM
Please respond to
peter.braw...@earthlink.net
To
M
Huh???
On Saturday, June 20, 2009, Peter Brawley wrote:
> Mike
>
>>J holding the next integer that T has for S
>
> You mean for each i, the next value of i with that s?
>
>>(U having no row for the last integer of each string).
>
> I do not understand that at all.
>
> PB
>
>
> Mike Spreitzer wrot
Mike
>J holding the next integer that T has for S
You mean for each i, the next value of i with that s?
>(U having no row for the last integer of each string).
I do not understand that at all.
PB
Mike Spreitzer wrote:
Suppose I have a table T with two column, S holding strings (say,
VARCHA
