Hi,
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
FROM names
ORDER BY main_id, sub_id ASC;
David J.
