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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Pawan KumarDatabase ExpertCommented:
Are you using mySQLServer or MS SQL Server ?
2
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Aimee KatherineAuthor Commented:
Sorry, I'm using mySQLServer.  Thanks.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
PortletPaulEE Topic AdvisorCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.