CODEWITHSUNDEEP
sqlpostgresqldistinct-on
bphi
bphi

Reputation: 3195

SELECT DISTINCT ON in Postgresql returns multiple rows

When I run 

SELECT DISTINCT ON (ts) * 
FROM tbl 
WHERE clause=100 
AND ts <= '2018-04-02 15:11:18.819000' 
AND ts > '2018-04-02 15:06:18.819000' 
ORDER BY ts, version ASC;

I expect a single row corresponding to the largest ts that meets the condition, with ties being broken by taking the lowest version.

The query returns

         ts                     version
'2018-04-02 15:07:04.828'   ...   1
'2018-04-02 15:07:05.706'   ...   1

I don't understand why two non-distinct ts are being returned. Is there a similar query that will return the desired result?

Upvotes: 0

Views: 1187

Answers (2)

Igor Cova
Igor Cova

Reputation: 3514

As I understand, do you want to get version and last ts

In your case it's better to use no DISTINCT but GROUP BY

For example this code can solve your problem:

SELECT max(ts), version 
FROM tbl 
WHERE clause=100 
  AND ts <= '2018-04-02 15:11:18.819000' 
  AND ts > '2018-04-02 15:06:18.819000' 
GROUP BY version;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Distinct on returns one row for each combination of the keys following the clause. In this case, each ts would appear once. Which one? That is determined by the order by. The keys following the distinct on keys determine the first, so that would be the lowest version number.

Your query appears to be producing reasonable results.

If you want one row in the result set with the largest ts, then use order by and limit/fetch first 1 row only:

SELECT * 
FROM tbl 
WHERE clause = 100 AND
      ts <= '2018-04-02 15:11:18.819000' AND
      ts > '2018-04-02 15:06:18.819000' 
ORDER BY ts DESC, version ASC
FETCH FIRST 1 ROW ONLY;

Note the ts DESC in the ORDER BY, to get the most recent value of ts.

Upvotes: 3

Related Questions