I'm using Vertica db and trying to sort the rows by DESC and ASC in which both gives the same output. The rows value are in date and time like shown below.
testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' desc limit 5;
queue_entry_timestamp | acquisition_timestamp | queue wait
-------------------------------+-------------------------------+-----------------
2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00 | 00:00:00.000029
2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)
testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' asc limit 5;
queue_entry_timestamp | acquisition_timestamp | queue wait
-------------------------------+-------------------------------+-----------------
2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00 | 00:00:00.000029
2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)
Could someone let me know how to sort this ?
The problem with your query is the order by syntax:
ORDER BY 'queue_entry_timestamp'
This actually says to order by a constant string literal 'queue_entry_timestamp'
. This value will always be the same, for every record in your table. So your current logic really has no ordering at all. Use this version:
ORDER BY queue_entry_timestamp