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: [email protected]
 --To: [email protected],[email protected],[email protected]
 --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: [email protected]
 --To: [email protected],[email protected]
 --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
.