I've been trying various GROUP BY but these all end up erroring out, so maybe I'm using the wrong tool for the job (or, more likely, the required query is beyond my level of SQL-fu !).
CREATE TABLE IF NOT EXISTS names ( main_id bigint, sub_id bigint, name text );
create unique index IF NOT EXISTS name_idx on names(main_id,sub_id); insert into names(main_id,sub_id,name) values(1,2,'Bob'); insert into names(main_id,sub_id,name) values(1,1,'Baby Bob'); insert into names(main_id,sub_id,name) values(100,200,'Tom'); insert into names(main_id,sub_id,name) values(100,100,'Jerry');
The desired output is one row per main_id, with the desired row being the one with the lowest sub_id, i.e.
select.......
main_id | sub_id | name ---------+------------+------------------- 1 | 1 | Baby Bob 100 | 100 | Jerry
Try (not tested):
SELECT DISTINCT ON (main_id) main_id, sub_id, name