CREATE TABLE employee
(
    joining_date date,
    employee_type character varying,
    name character varying ,
  typeid integer
);
insert into employee VALUES
    ('2021-08-12','as','hjasghg', 1),
    ('2021-08-13', 'Rs', 'sa', 1),
    ('2021-08-14','asktyuk','hjasg', 1),
  ('2021-09-12','as','hjasghg', 1),
    ('2021-09-13', 'Rs', 'sa', 1),
    ('2021-09-14','asktyuk','hjasg', 1),
  ('2022-08-02','as','hjasghg', 2),
    ('2022-08-03','as','hjasghg', 2),
    ('2022-08-04', 'Rs', 'sa', 2),
    ('2022-08-05','asktyuk','hjasg', 2);

I want to obtain the columns containing reading of first data of each month for different typeid.

I have tried applying partition but I can't seem to extract the correct data.

 with cte_a as
    (select row_number() over (partition by typeid order by joining_date asc) sno, * from employee)
    select * from cte_a where sno = 1;

I expected results from date '2021-09-12','2021-08-12','2022-08-02', but I only got '2022-08-02','2021-08-12' in final result.

CodePudding user response：

demo:db<>fiddle

You could use DISTINCT ON in combination with date_part() function

SELECT DISTINCT ON (typeid, date_part('month', joining_date))
    *
FROM employee
ORDER BY typeid, date_part('month', joining_date)

1. date_part('month', ...) "normalizes" the date to the first of a month (or cut the day part, if you like)
2. DISTINCT ON returns the first of all ordered groups, in that case the group of typeid and your normalized date