MySQL Forums
Forum List  »  Falcon

Re: Fastest Way to Do Row-Number Operations?
Posted by: Ann Harrison
Date: April 12, 2007 04:26PM

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, 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.



Options: ReplyQuote

Written By
Re: Fastest Way to Do Row-Number Operations?
April 12, 2007 04:26PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.