I have a query where I need to select the latest 25 entries to my database, but inverse the order of the results of that select.
I've come up with:
SELECT indexID, datetime, temperature FROM dataB WHERE userID="4236" ORDER BY indexID DESC, datetime ASC LIMIT 25
But I'm still getting the results in chronological order starting from newest, but I want chronological oldest to newest WITHIN those 25 newest. I'm using PHP's pg_fetch_row() and creating a concatenated string with the results and feeding that into a Morris.js graph, where my data is being graphed backwards because of this query.
How do I reverse the results of a query?
You should try this , first fetch data in descending orders after that use as temporary table and again select in ascending order
SELECT indexID, datetime, temperature FROM (SELECT indexID, datetime, temperature FROM dataB WHERE userID="4236" ORDER BY indexID DESC LIMIT 25) temp order by indexID ASC
Instead of putting the results directly into a string, load them up in an array. When the array is full, use the array_reverse function to reverse the order of the elements in the array. You can now build your string and pass the data to your Morris graph.
Documentation: http://php.net/manual/ro/function.array-reverse.php
Try to add an offset along with a limit to your query. Below example shows an offset which basically gets the total number of rows that match your query and subtracts 25 from it to offset you to start from the "25th last" record which matches your query:
SELECT indexID, datetime, temperature
FROM dataB WHERE userID="4236"
ORDER BY indexID DESC, datetime ASC
LIMIT (( SELECT COUNT(indexID) FROM dataB WHERE userID="4236" )-25), 25;