RE:RE:Re: A complex SQL query - Mailing list pgsql-sql

To Janis or other members of the forum:

My sample table contains other columns as well.

One of the columns that I did not include in my first message is "position".
The last table has been modified as follows:

+--------+----------+------------+------------------------+
| usr_id | employer | position   + start_date | end_date  |
+--------+----------+------------+------------------------+
| A      | Goo      | CTO        |            | 201904    |        
| A      | Micro    | Mgr        | 201704     | 201903    |

| B      | Micro    | Engg_Mgr   | 201706     | -         |
| B      | Goo      | Researcher | 201012     | 201705    |
| B      | Micro    | Postdoc    | 201001     | 201011    |
+--------+----------+------------+------------------------+

Explanation:
B joined Micro as a postdoc in 201001. After 11 months (201011), he quit and moved to Goo as a researcher.
After about 6.5 years, he came back to Micro, this time, as an engineering manager.

Problem:
I want to retrieve position as well, but I cannot add 'position' to the select part.
//----------------------------------------------------------------------------------
select
   user_id,position,
   max(position_start) as position_start,
case when max(position_end) < max(position_start) then null else max(position_end) end as position_end
            from
                employer
            where
                employer= 'Micro'
            group by
                user_id;
           
//-------------------------------------

This results in error:

"ERROR:  column "employer.position" must appear in the GROUP BY clause or be used in an aggregate function


When I add position to the group by clause, like this:
//----------------------------------------------------------------------------------
select
   user_id,position,
   max(position_start) as position_start,
case when max(position_end) < max(position_start) then null else max(position_end) end as position_end
            from
                employer
            where
                employer= 'Micro'
            group by
                user_id, position;
           
//-------------------------------------

I get two records for B, both when he was a postdoc back in 201001, and Engg_Mgr (201706).
I only need the latest/single entry (the whole point I wanted to do MAX(start_date).

How could I do that?

Thank you.

--From: pgsql-sql-owner+M68182-176663@lists.postgresql.org
--To: jj08@drivehq.com,pgsql-sql@lists.postgresql.org,janis@puris.lv
--Date: 9/6/2019 7:35:30 AM
--Subject: RE:Re: A complex SQL query

-------------------------
Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and More.
Drive Headquarters. Top quality services designed for business!
Sign up free at: www.DriveHQ.com.