Inner join using distinct on

Question

create table emp
(
   emp_id serial primary key,
   emp_no integer,
   emp_ref_no character varying(15),
   emp_class character varying(15),
   created_at timestamp,
   created_by character varying(20)
);

create table emp_detail
(
   emp_detail_id serial primary key,
   emp_id integer,
   class_no integer,
   col1 JSONB,
   col2 JSONB,
   col3 JSONB,
   created_at timestamp without time zone default now(),
   created_by character varying(20),
   constraint con_fk foreign key(emp_id) references emp(emp_id)
 );

INSERT INTO emp(
            emp_no, emp_ref_no, emp_class, created_by)
    VALUES ('548251', '2QcW', 'abc', 'Nik');

INSERT INTO emp_detail(
            emp_id, class_no, created_at, 
            created_by)
    VALUES ( 1, 1, '2018-05-04 11:00:00', 
            'Nik'); 

INSERT INTO emp_detail(
            emp_id, class_no, created_at, 
            created_by)
    VALUES ( 1, 1, '2018-04-04 11:00:00', 
            'Nik'); 

INSERT INTO emp_detail(
            emp_id, class_no, created_at, 
            created_by)
    VALUES ( 1, 2, '2018-05-10 11:00:00', 
            'Nik');

INSERT INTO emp_detail(
            emp_id, class_no, created_at, 
            created_by)
    VALUES ( 1, 2, '2018-02-01 11:00:00', 
            'Nik');

I want to display corresponding emp_id, emp_no, emp_ref_no along with all the columns of emp_detail table. Emp_detail table should be group by class no and should only show the record which has max (created_at) I am trying to use distinct on but does not give desired result

select e.emp_id, e.emp_no, e.emp_ref_no, ed.* from emp e inner join emp_detail ed on e.emp_id=ed.emp_id
where emp_detail_id in(select distinct on (ed.class_no) ed.emp_detail_id
from emp_detail ed
order by ed.class_no, created_at desc) and e.emp_no=548251;

S-Man · Accepted Answer · 2019-01-28T08:15:18.980

1

Is this what you expect?

|emp_id|emp_no|emp_ref_no|emp_detail_id|emp_id|class_no|col1  |col2  |col3  |created_at         |created_by|
|------|------|----------|-------------|------|--------|------|------|------|-------------------|----------|
|     1|548251|2QcW      |            1|     1|       1|[NULL]|[NULL]|[NULL]|2018-05-04 11:00:00|Nik       |
|     1|548251|2QcW      |            3|     1|       2|[NULL]|[NULL]|[NULL]|2018-05-10 11:00:00|Nik       |

demo:db<>fiddle

SELECT DISTINCT ON (ed.class_no)
    e.emp_id, e.emp_no, e.emp_ref_no, ed.*
FROM 
    emp_detail ed
JOIN emp e ON e.emp_id = ed.emp_id
ORDER BY ed.class_no, ed.created_at DESC

If you want to use DISTINCT ON (any group) you have to order by this group and after that you need to define the order within these groups. So your groups are the class_no and the criteria to group row within the class_no is the created_at timestamp. So your ORDER BY clause is (class_no, created_at DESC). Then DISTINCT ON filters out exactly the first row of each ordered group, in that case the last recent created one is the first.

edited Jan 28 '19 at 08:15

answered Jan 28 '19 at 08:03

S-Man

22,521
7
40
63

Is it possible to apply DISTINCT ON on candidate key? – Nik Jan 28 '19 at 11:19
You mean DISTINCT ON (e.emp_id)? – S-Man Jan 28 '19 at 11:24
There are instances where emp_no and emp_ref no are unique and needs to be displayed in output and for that corresponding emp_detail needs to be displayed – Nik Jan 28 '19 at 11:30
Please add the expected output to your question for demonstrating – S-Man Jan 28 '19 at 13:10
https://stackoverflow.com/questions/54403420/inner-join-on-multiple-tables-using-distinct-on I have raised new question. – Nik Jan 28 '19 at 13:50
Please do not open a second question for that. It's nearly the same thing just a join more. and please always add a the expected output. Please minimize your examples. there are many columns we do not need. – S-Man Jan 28 '19 at 13:55

Inner join using distinct on

1 Answers1

Linked