Why does distinct(column) return a duplicate when added with order by?

Question

The SQL:

select distinct
    h.teacher_id,
    h.last_updated_at,
    c.name
from sometable h 
   inner join class c on h.teacher_id=c.id
   where last_updated_at is not null
   order by last_updated_at desc limit 5;

I'm getting duplicate teacher_id in response. Where am I going wrong? I wanted to return last updated teacher name and id.

You are misunderstanding what `distinct` does. It applies to the entire row. — Gordon Linoff, May 09 '21 at 11:58

Erwin Brandstetter · Answer 1 · 2021-05-09T12:35:05.130

0

SELECT DISTINCT * FROM ... returns unique rows. The same teacher can appear many times while other columns differ.

If you want rows to be unique on selected column(s), use DISTINCT ON. Like:

SELECT DISTINCT ON (teacher_id)
       h.teacher_id,
     , h.last_updated_at
     , c.name
FROM   ...
ORDER  BY teacher_id, last_updated_at DESC
LIMIT  5;

Detailed explanation:

Select first row in each GROUP BY group?

If you want a different ORDER BY, see:

PostgreSQL DISTINCT ON with different ORDER BY

edited May 09 '21 at 12:35

answered May 09 '21 at 11:41

Erwin Brandstetter

605,456
145
1,078
1,228

Yes I did that too. If teacher_id is not just id, its a unique string id. So in order by when I add teacher_id the entire thing changes. – Bharat Bittu May 09 '21 at 11:43
@BharatBittu: The linked answer explains every last detail of `DISTINCT ON`. – Erwin Brandstetter May 09 '21 at 11:45

score 0 · Answer 2 · answered May 09 '21 at 11:44

You will probably see that the teacher_id in question has more than one class associated with it. If you need one record per teacher and all classes they lead, you can use something like STRING_AGG(c.name) (depending on your version of PostgreSQL). Alternatively, you can return the number of classes they lead with a COUNT(c.name).

In both cases, you will need to GROUP BY the teacher’s ID and last update.

Why does distinct(column) return a duplicate when added with order by?

2 Answers2