I have created a table employee and while using select query I have used partition on the same column but used order by in two different orders .

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),
    ('2022-08-12', 'Rs', 'sa', 1),
    ('2023-08-12','asktyuk','hjasg', 1),
  ('2023-08-02','as','hjasghg', 2),
    ('2022-08-02','as','hjasghg', 2),
    ('2022-08-02', 'Rs', 'sa', 2),
    ('2022-08-02','asktyuk','hjasg', 2);
    
  select row_number() over (partition by typeid order by joining_date asc) sno,
     row_number() over (partition by typeid order by joining_date desc) slno,* from employee;

Now what I require is the joining date as date_1 when sno = 1 and joining date as date_2 when slno = 1 alongside typeid in the query output. I need to use a single query to obtain the desired output, how should I proceed.

CodePudding user response：

You're currently selecting the highest and lowest date using the ROW_NUMBER window functions, that you want to extract in separate dates "date1" and "date2" for each "typeid". The next step would require a pivot, with:

• CASE expressions, that extract "joining_date" values for each "date1", "date2" field
• MAX aggregation, that remove the NULL values

WITH cte AS (
    SELECT typeid, 
           joining_date,
           ROW_NUMBER() OVER(PARTITION BY typeid ORDER BY joining_date ASC ) sno,
           ROW_NUMBER() OVER(PARTITION BY typeid ORDER BY joining_date DESC) slno
    FROM employee 
)
SELECT typeid,
       MAX(CASE WHEN sno  = 1 THEN joining_date END) AS date1,
       MAX(CASE WHEN slno = 1 THEN joining_date END) AS date2
FROM cte
GROUP BY typeid

Check the demo here.

Although, a simpler way is using an aggregation with the MIN and MAX aggregate functions.

SELECT typeid,
       MIN(joining_date) AS date_1,
       MAX(joining_date) AS date_2
FROM employee
GROUP BY typeid

Check the demo here.