PostgreSQL DISTINCT ON

Question

I am not able to define an alias or to choose specific columns, works only with wildcard:

Working:

SELECT DISTINCT ON (r.InsertDate) *  
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

Not working :

SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Servername 
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

Error

ERROR:  column "lastreport" does not exist
LINE 1:  SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Serve...
                                           ^
SQL state: 42703
Character: 36

Also not working:

SELECT DISTINCT ON (r.InsertDate) , s.Id, s.Servername 
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

ERROR:  column "lastreport" does not exist
LINE 1:  SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Serve...
                                           ^
SQL state: 42703
Character: 36

ERROR:  syntax error at or near ","
LINE 1:  SELECT DISTINCT ON (r.InsertDate) , s.Id, s.Servername 
                                           ^
SQL state: 42601
Character: 36

Any idea?

score 0 · Answer 1 · answered Jun 11 '20 at 11:24

You seem to be confused about DISTINCT ON. It does not "return a column". It is a syntactic construct. These are variations of the SELECT:

SELECT ALL
SELECT DISTINCT
SELECT DISTINCT ON ()

(The first is the default and never used in practice.)

The column list follows these constructs. So, apparently you want:

SELECT DISTINCT ON (r.InsertDate) r.InsertDate as LastReport, s.Id, s.Servername 
FROM public.server s LEFT JOIN
     public.Report r
     ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

It turns out that DISTINCT ON is actually parsed after the rest of the SELECT clause, so you can use column aliases:

SELECT DISTINCT ON (LastReport) r.InsertDate as LastReport, s.Id, s.Servername 
FROM public.server s LEFT JOIN
     public.Report r
     ON s.Id = r.ServerId
ORDER BY LastReport DESC;

However, the alias must be defined in the column list after the distinct on.

Note that you are using a LEFT JOIN, so r.InsertDate could be NULLL.

PostgreSQL DISTINCT ON

1 Answers1