Simple query

gistfile1.txt

drop table person;

CREATE TABLE person
(
  id integer NOT NULL,
  "name" character varying,
  code integer,
  CONSTRAINT pk_person PRIMARY KEY (id)
);

INSERT INTO person(id, "name",code)VALUES (1,'John',100);
INSERT INTO person(id, "name",code)VALUES (2,'John-second',100);
INSERT INTO person(id, "name",code)VALUES (3,'John-third',100);
INSERT INTO person(id, "name",code)VALUES (4,'Mike',101);
INSERT INTO person(id, "name",code)VALUES (5,'Jennifer',102);

--How do I transform this query so that I get the logic "if more than 2 entities with the same code exist, give me just
-- the clostest relationship", where closest means "closest id's". in this case, we only want the results "john/john-second" and "john-second/john-third".

select * from person p1
inner join person p2 on p2.code=p1.code and p1.id<>p2.id
where p1.id<p2.id

--Solution:

select p1.*,p2.* from person p1
inner join (select p2.id,max(p1.id) as max_p1 from person p1 inner join person p2 on p2.code=p1.code and p1.id<>p2.id
where p1.id<p2.id group by p2.id) p2_glue on p2_glue.id=p1.id
inner join person p2 on p2.id=p2_glue.max_p1