2

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?

T1960CT
  • 407
  • 7
  • 18

3 Answers3

2

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
Vinod Kumawat
  • 741
  • 5
  • 8
  • I am sure it will work, I have edited it i did little bit mistake in query but now it is ok – Vinod Kumawat Sep 08 '17 at 18:48
  • I hadn't thought about doing it this way, it's logical and clean, but I just don't understand where the "temp" is coming from, as it's throwing an error.`ERROR: column temp.indexid does not exist` – T1960CT Sep 08 '17 at 19:04
  • 1
    (SELECT indexID, datetime, temperature FROM dataB WHERE userID="4236" ORDER BY indexID DESC LIMIT 25) order by indexID Try this also I hope it will work – Vinod Kumawat Sep 08 '17 at 19:11
  • `ERROR: subquery in FROM must have an alias` – T1960CT Sep 08 '17 at 19:14
  • I'll keep tinkering with it though, you pointed me in the right direction, Vinod – T1960CT Sep 08 '17 at 19:15
  • SELECT indexID, datetime, temperature FROM (SELECT indexID, datetime, temperature FROM dataB WHERE userID="4236" ORDER BY indexID DESC LIMIT 25) as temp order by temp.indexID ASC I am not sure but I think you should try this also – Vinod Kumawat Sep 08 '17 at 19:18
  • I ended up getting it with this: `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` I'll mark your answer as correct since it was close enough and led to getting it working. – T1960CT Sep 08 '17 at 19:34
  • You know you're allowed to use newlines in SQL queries ;-) – Indiana Kernick Dec 14 '20 at 04:56
0

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

Andrei
  • 1,723
  • 1
  • 16
  • 27
  • Unfortunately, the way I'm feeding the data into Morris.js, it makes it a string. array_reverse gets real angry about that since it's not an array anymore. – T1960CT Sep 08 '17 at 18:54
0

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;
coderodour
  • 1,072
  • 8
  • 16
  • The database this is coming from will be growing to a very large size, wouldn't this take a longer and longer time to process? – T1960CT Sep 08 '17 at 19:05