DISTINCT work differntly in MYSQL & POSTGRESQL

I have created a sample table report in SQL and filled sample data in it using the following command.

create table report(id int primary key,vistor_id int, branch_id int,date int);
insert into report values (1,1,3,27),(2,1,2,27),(3,1,1,28),(4,1,4,30),(5,1,1,30);

I need to find the list of recently visited(based on date column) branches with out duplication. So I used the following query

select distinct branch_id from report order by date desc;

It works on MYSQL but shows the following error on POSTGRESQL. How to fix this? Or How can I obtain the same result in POSTGRESQL?(The error is from sqlfiddle.com).

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 48

Answer

Your query is not valid standard SQL. It works in MySQL only if you have option ONLY_FULL_GROUP_BY disabled.

The problem is that there may be multiple dates per branch_id: which one should be used for ordering?

You can use aggregation and be explicit about what you ask for. Say you want to order by the latest date per branch_id:

select branch_id from report group by branch_id order by max(date) desc