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
Aimee KatherineAsked:
Who is Participating?
 
Ryan ChongCommented:
as far as what i know, MySQL don't come with Row_Number syntax, you got to join the source table in order to get sequential numbers or use method below

for ref:

MySQL row_number, This Is How You Emulate It
http://www.mysqltutorial.org/mysql-row_number/
2
 
Ryan ChongCommented:
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
2
 
Aimee KatherineAuthor Commented:
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...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
Are you using mySQLServer or MS SQL Server ?
2
 
Aimee KatherineAuthor Commented:
Sorry, I'm using mySQLServer.  Thanks.
0
 
Aimee KatherineAuthor Commented:
Ok thanks again for the feedback.  So really my best bet is to stick with LIMIT and OFFSET?
0
 
Pawan KumarDatabase ExpertCommented:
In mySQL Server there are no built in functions like Row_Number(), rank(), dense_rank() etc.

>>So really my best bet is to stick with LIMIT and OFFSET?
Try if it fits the thing.

But we can generate these using our logic like below.

SET @row_number = 0;
SELECT ID            ,CustID  ,Created_Date   ,Value , num RowNumber FROM
(
    SELECT *,(@row_number:= @row_number + 1) AS num
    FROM 
    (
        SELECT *
        FROM
        (
            SELECT 1 ID ,1000 CustID,   STR_TO_DATE('20161210 105015','%Y%m%d %h%i') Created_Date,99 Value UNION ALL 
            SELECT 2  ,1000 ,   STR_TO_DATE('20161210 105010','%Y%m%d %h%i') ,99  UNION ALL 
            SELECT 3  ,1000 ,   STR_TO_DATE('20161210 105030','%Y%m%d %h%i') ,99  UNION ALL 
            SELECT 4  ,1000 ,   STR_TO_DATE('20161209 085030','%Y%m%d %h%i') ,99  UNION ALL 
            SELECT 5  ,1000 ,   STR_TO_DATE('20161209 084930','%Y%m%d %h%i') ,99  UNION ALL 
            SELECT 6  ,1000 ,   STR_TO_DATE('20161208 074530','%Y%m%d %h%i') ,99  UNION ALL 
            SELECT 7  ,1000 ,   STR_TO_DATE('20161208 074530','%Y%m%d %h%i') ,99
        )k 
    )P,(SELECT @row_number:=0) as t
)Z
ORDER BY ID

Open in new window

2
 
Ryan ChongCommented:
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/
2
 
Aimee KatherineAuthor Commented:
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.
2
 
PortletPaulfreelancerCommented:
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.
3
 
Pawan KumarDatabase ExpertCommented:
Welcome Aimee !

Always glad to help :)
1
 
Ryan ChongCommented:
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
2
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.