在排序时，postgres忽略了"-“

Question

我正在整理这些值。波斯特格斯忽略了"-“。以下是我的查询：

select 0 as key, 
       '------ select ------' as value 
union 
SELECT contact_replica_child.contact_id as key,
       contact_replica_child.last_name||', '||contact_replica_child.first_name as value 
FROM contact_replica_child 
  join listing_replica_child on contact_replica_child.administrative_agency_id = listing_replica_child.agency_id 
where listing_replica_child.session_id = '3edfa73687a53604a50708d3d5d90221' 
order by value ;

我明白了：

  key   |          value          
--------+-------------------------
 581489 | Contact, Administrative
 581490 | Green, Kelley
      0 | ------ select ------

我期待着：

  key   |          value          
--------+-------------------------
      0 | ------ select ------
 581489 | Contact, Administrative
 581490 | Green, Kelley

有解决办法吗？

Answer 1

虽然我必须承认我不明白为什么Postgres是这样做的，但是您可以轻松地将order by子句限制在第二个查询中，使用括号：

SELECT    0 AS key, '------ select ------' AS value 
UNION ALL
(SELECT   contact_replica_child.contact_id AS key,
          contact_replica_child.last_name || ',' ||  contact_replica_child.first_name AS value 
 FROM     contact_replica_child 
 JOIN     listing_replica_child ON 
          contact_replica_child.administrative_agency_id = 
          listing_replica_child.agency_id
 WHERE    listing_replica_child.session_id = '3edfa73687a53604a50708d3d5d90221' 
 ORDER BY value
);