How to select multiple columns grouping by with only one column

Question

I would like to retrieve all the data of emp detail table grouping by class_ no based on max(created_at). Also I don't want to group by data with any other column as it changes the final result. Also, Is there any solution to retrieve the same result without even using group by clause

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');

Gordon Linoff · Accepted Answer · 2019-01-24 12:51:35Z

4

I think you want distinct on:

select distinct on (ed.class_no) ed.*
from emp_detail ed
order by ed.class_no, created_at desc;

distinct on is a very handy Postgres extension. This is probably the fastest method, especially with an index on emp_detail(class_no, created_at desc).

Other alternatives are a subquery with row_number() or a correlated subquery.

answered Jan 24, 2019 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nik Over a year ago

You are genius!

Collectives™ on Stack Overflow

How to select multiple columns grouping by with only one column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related