Postgresql - Distinct On Result

Question

I have already used 'distinct on' in query, but the result is still duplicate. Can i only get two result from below picture? e.g: row 1 & 4 or 2 & 3, two rows is different in id and serial number.

Answer 1

Your rows are already distinct. If you meant to be distinct only based on id, then from what row the other values would come from? If it is not important then what you need is a group by on Id and some dummy aggregation on other columns (ie; max(return_document), max(sales_document), max(serial_number)). Or alternatively you could do this:

select id,return_document, sales_document, serial_number
from (
select row_number() over (partition by id order by id) as rno, *
from myTable) tmp
where rNo = 1;

You are arbitrarily selecting one of the rows per Id. Or with ppostgreSQL's Distinct on:

SELECT DISTINCT ON (id) id, return_document, sales_document, serial_number 
FROM myTable;

id	return_document	sales_document	serial_number
88687	RFC-01-2300082	SO-01-22002077	R1H-03490
88688	RFC-01-2300082	SO-01-22002077	R1H-03490

DBFiddle demo

EDIT: This would work for this very specific data set, it might not for another (and you only gave this):

with umatch as (select *
                from (select id, row_number() over (order by id) rn from (select distinct id from myTable) t1) _t1
                         inner join
                     (select serial_number, row_number() over (order by serial_number) rn
                      from (select distinct serial_number from myTable) t2) _t2 on _t1.rn = _t2.rn)
select t2.*
from umatch t1
         inner join myTable t2 on t1.serial_number = t2.serial_number and t1.id = t2.id;

DBFiddle demo

Please next time be clear on what you are asking and supply enough data and desired output as text (people don't like pictures on SO).