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 "p= osition".
The last table has been modified as follows:

+--------+----------+------------+------------------------+
| usr_id | employer | position   + start_date | end_date = |
+--------+----------+------------+------------------------+
| A      | Goo      | = CTO        |    &= nbsp;       | 201904    |&n= bsp;
| A      | Micro    | Mgr &= nbsp;      | 201704     | 2= 01903    |

| B      | Micro    | Engg_Mgr&n= bsp; | 201706     | -    &= nbsp;    |
| B      | Goo      | = Researcher | 201012     | 201705    |=
| B      | Micro    | Postdoc&nb= sp;   | 201001     | 201011  &nb= sp; |
+--------+----------+------------+------------------------+

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 engineeri= ng 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(pos= ition_end) end as position_end
            from<= br />             =     employer
            where=
            =     employer=3D 'Micro'
            group= by
            =     user_id;

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

This results in error:

"ERROR: column "employer.position" must appear in t= he 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(pos= ition_end) end as position_end
            from<= br />             =     employer
            where=
            =     employer=3D '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

Yes, that's exactly what works for me.

Thanks a bunch!!!

--From: janis@puris.lv
--To: pgsql-sql@lists.postgresql.org,jj08@drivehq.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/2= e45eb/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

from

employment

where

employer =3D '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.