Link to home
Start Free TrialLog in
Avatar of Aimee Katherine
Aimee Katherine

asked on

How can I add ROW_NUMBER() into my query that has joins?

Hi,
I am looking to paginate the results of the query, so I need to use
ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter
and then put:
WHERE   RowNum >= 1
    AND RowNum < 20

but I am not sure how to add it into this query that already has two joins.
Here is my current query:

$data_query = mysqli_query($conn, "
SELECT ZIPCodes.zipcode, usersearch_answers.username,
       (3959 * acos(cos(radians(ZIPCodes.latitude)) *
       cos(radians(center.latitude)) *
       cos(radians(ZIPCodes.longitude ) -
       radians(center.longitude)) +
       sin(radians(ZIPCodes.latitude)) *
       sin(radians(center.latitude)))) AS distance
FROM
(
    (
        SELECT usersearch_answers.username,
            usersearch_answers.zipcode,
            ZIPCodes.latitude,
            ZIPCodes.longitude
        FROM
        (
            usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode = ZIPCodes.zipcode
        )    
        WHERE (ZIPCodes.zipcode='33133')
    ) center, ZIPCodes
) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.username <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");

How do you combine the two?
Any tips are so greatly appreciated!  
-Aimee
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

is your database MySQL?

if yes, try to add limit clause at the end of your Select statement, like:

ORDER BY distance LIMIT 20

LIMIT Query Optimization
https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
Avatar of Aimee Katherine
Aimee Katherine

ASKER

Hi, thank you so much for the feedback.  I was told that performance would be better if I used the WHERE clause in this case...
Are you using mySQLServer or MS SQL Server ?
Sorry, I'm using mySQLServer.  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok thanks again for the feedback.  So really my best bet is to stick with LIMIT and OFFSET?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So really my best bet is to stick with LIMIT and OFFSET?
it would be easy to do that, or follow the tutorial as posted in https:#a42401953:
http://www.mysqltutorial.org/mysql-row_number/
Thank you so much, Ryan Chong and Pawan Kumar. Those look like perfect  solutions!  I would just need help putting it into my existing query.  =) I had read that large offsets would hinder performance, so that's why I wanted to make use of the order of IDs with the WHERE clause.
This is purely as an FYI.

MySQL has been slow to adopt "window functions" such as row_number(), lead(), lag() etc. but version 8 of MySQL (when ready for production use) should have these available.

Personally while you can mimic the pagination offered by LIMIT and OFFSET using @variables, I would suggest you use LIMIT and OFFSET for performance, LIMIT and OFFSET are designed for pagination.

The @variables technique, whilst functionally very handy for other purposes (like finding the "first date" or the "last update") this technique will be replaceable by row_number() when available.
Welcome Aimee !

Always glad to help :)
MySQL has been slow to adopt "window functions" such as row_number(), lead(), lag() etc. but version 8 of MySQL (when ready for production use) should have these available.
Glad to know Paul and that's one of the benefits to upgrade the database server :)

and here's the Pre-General Availability Draft
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html