SELECT DISTINCT ON expressions must match initial ORDER BY expressions

问题描述：

PostgreSQL 中使用distinct on() 分组时报错

// sql语句
select DISTINCT ON(p.person_id)
p.person_id       as personId,
               p.person_type     as personType,
               p.job_number      as jobNumber,
               p.name,
               p.idcard          as idcard,
               p.sex,
               p.company,
               p.owner_company   as ownerCompany,
               p.phone,
               p.status,
               p.update_time,
               p.create_time
        from smc_person p
                     left join smc_car sc on ((sc.driver ~ p.person_id::VARCHAR) = 't' and sc.status = '1')
                where p.status = '1'
        order by p.create_time desc, p.update_time desc nulls last
        , p.name asc

此时报错：
SELECT DISTINCT ON expressions must match initial ORDER BY expressions

---

原因分析：

查询官方文档： DISTINCT ON（expression [，...]）仅保留给定表达式求值的每组行的第一行等于。 [...]注意，除非使用ORDER BY来确保所需的行首先出现，否则每个集合的"第一行"都是不可预测的。 [...] DISTINCT ON表达式必须与最左边的ORDER BY表达式匹配。 ----------DISTINCT ON 中的字段，必须位于order by 的最左边

---

解决方案：

我不想根据p.person_id来排序。可以采用如下写法：

SELECT * FROM(
select DISTINCT ON(p.person_id)
p.person_id       as personId,
               p.person_type     as personType,
               p.job_number      as jobNumber,
               p.name,
               p.idcard          as idcard,
               p.sex,
               p.company,
               p.owner_company   as ownerCompany,
               p.phone,
               p.status,
               p.update_time,
               p.create_time
        from smc_person p
                     left join smc_car sc on ((sc.driver ~ p.person_id::VARCHAR) = 't' and sc.status = '1')
                where p.status = '1')t
        order by p.create_time desc, p.update_time desc nulls last, p.name asc