Re: Fastest Way to Do Row-Number Operations?

[Ann Harrison]

SET @rowcount=0;

I don't know whether this will help, but have you considered
eliminating the outer select and using a HAVING clause?

SELECT @rowcount:=@rowcount+1 AS rc,
p.person_id AS personID,
p.last_name AS lastName,
p.first_name AS firstName,
p.city AS city,
p.state AS state
FROM person p
GROUP BY lastName, personID
HAVING (tMiddle.rc-1) % 15=0
OR (tMiddle.rc) % 15=0
OR (tMiddle.rc) = 19261;


The query as written won't be portable to other databases
(but who would want to?) because your select list references
scalar fields not in the GROUP BY clause. I eliminated the
two ORDER BY clauses. The GROUP BY will have the side-effect
(I hope) of presenting all results in order by last name and
person id. Since rc is always increasing as records are
found, I think you don't need taht ORDER BY either.

If that doesn't work for you, could you post the result of
an EXPLAIN on the query? I suspect that Falcon isn't getting
the whole query and need the explain to understand what we
are getting.

Regards,


Ann