RE:RE:Re: A complex SQL query

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<br />
from
employer
where
employer= 'Micro'
group by
user_id;

//-------------------------------------

This results in error:

&quot;ERROR: column &quot;employer.position&quot; 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<br />
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(at)lists(dot)postgresql(dot)org
--To: jj08(at)drivehq(dot)com,pgsql-sql(at)lists(dot)postgresql(dot)org,janis(at)puris(dot)lv
--Date: 9/6/2019 7:35:30 AM --Subject: RE:Re: A complex SQL query

Yes, that's exactly what works for me.

Thanks a bunch!!!

--From: janis(at)puris(dot)lv
--To: pgsql-sql(at)lists(dot)postgresql(dot)org,jj08(at)drivehq(dot)com
--Date: 9/6/2019 5:56:10 AM --Subject: Re: A complex SQL query Would something like this work for you ? http://www.sqlfiddle.com/#!17/2e45eb/9 select user_id, max(start_date) as start_date, case when max(end_date) < max(start_date) then null else max(end_date) end as end_date</div> from employment where employer = 'Micro' group by user_id ; On 6 Sep 2019, 14:31 +0200, jj08

-------------------------
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.

-------------------------
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
.