Menu
I have the following table:
key | date | flag
--------------------------
1 now() true
2 now() - 1 hour true
3 now() + 1 hour true
4 now() false
5 now() - 1 hour false
6 now() + 1 hour false
I want the following sorting:
flag = false. These rows must be sorted with date asc.flag = true). However, these rows must be sorted with date desc.Is the following query correct?
(
select *
from test
where flag = false
order by date asc
)
union all
(
select *
from test
where flag = true
order by date desc
)
Is there a better way to do this? Will the union all keep the rows sorted, and therefore just concatenate the output of the two inner queries?
I don't know how to repeat the columns in the order by, based on a condition.
update
Fiddle can be found here: http://rextester.com/FFOSS79584
485
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.
has no guaranteed order. SELECT * FROM items ORDER BY published_date ASC; guarantees that two items with different dates come in a given order, but does not guarantee that two items with the same date always come in the same order.
ASC order is the default. Ascending order puts smaller values first, where “smaller” is defined in terms of the < operator.
The order of columns doesn't matter in creating tables in PostgreSQL, but it does matter sometimes in creating indexes in PostgreSQL.
conditional order can be performed with CASE, like here:
select *
from test
order by
flag
, case when flag then date end desc
, case when not flag then date end asc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
