Losing order rows with distinct - PostgreSQL

Question

I'm working with SQL and i want to select result with numero_engin if exist else numero_train. So i'm using coalesce to do this. To romove the duplicate rows, i tried to use distinct. But the problem when using distinct, i lose the order of rows.

Here is my request :

SELECT DISTINCT on (req.cle) req.cle,* 
  FROM (SELECT coalesce(p.numero_engin, p.numero_train) AS cle, * 
          FROM last_position p 
         WHERE 1=1 
           AND p.source_localisation LIKE 'B%' 
         ORDER BY p.numero_engin, p.utc_horodatage_observation DESC) AS req    
  ORDER BY req.cle,req.utc_horodatage_observation DESC

This request return the result ordered with the duplicate rows :

SELECT COALESCE(p.numero_engin, p.numero_train) AS cle, * 
  FROM last_position p 
 WHERE 1=1 
   AND p.source_localisation LIKE 'B%' 
 ORDER BY p.numero_engin, p.utc_horodatage_observation DESC

But the global request when adding distinct, display a disordered result.

No need to `ORDER BY` twice. Final `ORDER BY` in the two examples are not the same - hence the sorting will be different. — ssn, Aug 10 '17 at 07:50
I'm not sure I follow your problem, but of course `DISTINCT`, in general, cannot be order preserving. Imaging it's presented with three rows - in the first, the column is 1. In the second, 2. And in the third, 1. The final result will contain two rows. What *possible* order could it output those rows in the "preserves order"? — Damien_The_Unbeliever, Aug 10 '17 at 08:03
@Damien_The_Unbeliever in your exemple i add, in the first date=10/08/2017 in the second date=11/08/2017 and in the third, date=12/07/2017. So if i add order by date desc, the result is the third->second — youssef, Aug 10 '17 at 08:09

ssn · Accepted Answer · 2017-08-10T08:14:54.870

You need to rearrange

SELECT DISTINCT on (req.cle) req.cle,* 
  FROM (SELECT coalesce(p.numero_engin, p.numero_train) AS cle, * 
          FROM last_position p 
         WHERE 1=1 
           AND p.source_localisation LIKE 'B%' 
         ORDER BY p.numero_engin, p.utc_horodatage_observation DESC) AS req    
  ORDER BY req.cle,req.utc_horodatage_observation DESC

To

SELECT DISTINCT on (req.cle) req.cle,* 
  FROM (SELECT coalesce(p.numero_engin, p.numero_train) AS cle, * 
          FROM last_position p 
         WHERE 1=1 
           AND p.source_localisation LIKE 'B%' 
         ) AS req    
  ORDER BY p.numero_engin, p.utc_horodatage_observation DESC

Note that I removed the additional ORDER BY

EDIT: I notice that you use * in the select distinct, therefore you might not get what you expect. I cannot see your table design - but consider below example:

Table 1:

numero_engin | numero_train | something1 | something2
-----------------------------------------------------
engin1       | NULL         | something  | something
engin1       | numero1      | something1 | something2
NULL         | numero1      | something3 | something4

You then want an additional column with coalesce, you then get:

numero_engin | numero_train | something1 | something2 | coalesce_column
------------------------------------------------------------------------
engin1       | NULL         | something  | something  | engin1
engin1       | numero1      | something1 | something2 | engin1
NULL         | numero1      | something3 | something4 | numero1

So far so good - but now you want to have the distinct values from the coalesce_column - but because you use *, then you actually say that you want distinct rows.

So doing a SELECT DISTINCT coalesce_column FROM TABLE will get you what you want; but doing a SELECT DISTINCT coalesce_column, * FROM TABLE will get you above table, because each row IS distinct.

When using the astrix (*) you will get all columns, so trying to do the distinct onn the coalesce will not work. You need to specify the exact columns, otherwise you will get double results anyway. — ssn, Aug 10 '17 at 08:08
i tried this `select coalesce(p.numero_engin,p.numero_train) as cle, * from last_position p where 1=1 and p.source_localisation like 'B%' and cle in (select distinct coalesce(lp.numero_engin, lp.numero_train) from last_position lp where 1=1 and lp.source_localisation like 'B%') order by p.utc_horodatage_observation desc` but i have an error message `column cle not exist` — youssef, Aug 10 '17 at 09:20
Finallit works like that `select coalesce(p.numero_engin,p.numero_train) , * from last_position p where 1=1 and p.source_localisation like 'B%' and coalesce(p.numero_engin,p.numero_train) in (select distinct coalesce(lp.numero_engin, lp.numero_train) from last_position lp where 1=1 and lp.source_localisation like 'B%') order by p.utc_horodatage_observation desc` — youssef, Aug 10 '17 at 09:37

Losing order rows with distinct - PostgreSQL

1 Answers1