postgresql - 排序时 postgres 忽略 "-"

Question

我正在对值进行排序。 Postgres 忽略“-”。这是我的查询:

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
);