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.usernam e,
(3959 * acos(cos(radians(ZIPCodes. latitude)) *
cos(radians(center.latitud e)) *
cos(radians(ZIPCodes.longi tude ) -
radians(center.longitude)) +
sin(radians(ZIPCodes.latit ude)) *
sin(radians(center.latitud e)))) AS distance
FROM
(
(
SELECT usersearch_answers.usernam e,
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.userna me <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");
How do you combine the two?
Any tips are so greatly appreciated!
-Aimee
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.usernam
(3959 * acos(cos(radians(ZIPCodes.
cos(radians(center.latitud
cos(radians(ZIPCodes.longi
radians(center.longitude))
sin(radians(ZIPCodes.latit
sin(radians(center.latitud
FROM
(
(
SELECT usersearch_answers.usernam
usersearch_answers.zipcode
ZIPCodes.latitude,
ZIPCodes.longitude
FROM
(
usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode
)
WHERE (ZIPCodes.zipcode='33133')
) center, ZIPCodes
) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.userna
HAVING (distance < 5000)
ORDER BY distance");
How do you combine the two?
Any tips are so greatly appreciated!
-Aimee
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 ?
ASKER
Sorry, I'm using mySQLServer. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok thanks again for the feedback. So really my best bet is to stick with LIMIT and OFFSET?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
ASKER
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.
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 :)
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
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