From jj08@drivehq.com Fri Sep 6 12:31:28 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6DP4-0004nJ-I4 for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 12:31:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6DP2-00081x-Jv for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 12:31:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6DP2-0007xS-3f for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 12:31:48 +0000 Received: from mail02.mbizgroup.biz ([66.220.9.54] helo=mail.drivehq.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1i6DOy-0003s3-6h for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 12:31:45 +0000 Received: from (192.168.10.210) by EM244o80 with SMTP User ID0; Fri, 06 Sep 2019 12:31:28 GMT Received: from 126.200.125.235 (126.200.125.235) by mail.drivehq.com with HTTPUserID9736706; Fri, 06 Sep 2019 12:31:28 GMT X-Priority: 3 Message-ID: <2259317921000000009736706@www> From: "jj08" To: pgsql-sql@lists.postgresql.org Subject: A complex SQL query Date: Fri, 06 Sep 2019 05:31:28 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_225931792_9736706_637033446883894055" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_225931792_9736706_637033446883894055 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I hope someone can give me some pointers. Here is my table. +--------+----------+------------+-----------+ | usr_id | employer | start_date | end_date | +--------+----------+------------+-----------+ | A | Goo | 201904 | - | | A | Micro | 201704 | 201903 | = | B | Micro | 201706 | - | | B | Goo | 201012 | 201705 | | B | Micro | 201001 | 201011 | +--------+----------+------------+-----------+ = I am trying to list up people working for a company called "Micro&= quot;. Some people work for a company multiple times, like user B. I only need one line per user, displaying only the latest affiliation d= ate. = For user_id "B", I could do Select user_id, MAX(start_date), = end_date where employer =3D 'Micro', but that would fail to get record for user_id "A". = If multiple records exist, I want to do MAX, but if only a single recor= d exists, I don't need MAX. How do I do that? = From the above data, I would like to see only two lines: | A | Micro | 201704 | 201903 | | B | Micro | 201706 | - | Thank you. = ------------------------- Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and= More. = Drive Headquarters. Top quality services designed for business! Sign u= p free at: www.DriveHQ.com . = ------=_NextPart_225931792_9736706_637033446883894055 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

I = hope someone can give me some pointers.

Here is my table.

+--------+----------+------------+-----------+

+--------+----------+------------+-----------+

| A | Goo | 201904 &nbs= p; | - |

| A | Micro | 201704 &n= bsp;| 201903 |

| B | Micro | 201706 &n= bsp;| - |

| B | Goo | 201012 &nbs= p; | 201705 |

| B | Micro | 201001 &n= bsp;| 201011 |

+--------+----------+------------+-----------+

I am trying to list up people working for a company called "Micr= o".

Some people work for a company multiple times, like user B.

I only need one line per user, displaying only the latest affiliation= date.

For user_id "B", I could do Select user_id, MAX(start_date)= , end_date where employer =3D 'Micro',

but that would fail to get record for user_id "A".

If multiple records exist, I want to do MAX, but if only a single rec= ord exists, I don't need MAX.

How do I do that?

From the above data, I would like to see only two lines:

| A | Micro | 201704 &n= bsp;| 201903 |

| B | Micro | 201706 &n= bsp;| - |

Thank you.

-------------------------
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. =
------=_NextPart_225931792_9736706_637033446883894055-- From janis@puris.lv Fri Sep 6 12:56:02 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6Dmj-0005gm-0Y for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 12:56:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6Dmf-0006wg-9r for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 12:56:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6Dme-0006od-Nx for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 12:56:12 +0000 Received: from mail-lf1-x130.google.com ([2a00:1450:4864:20::130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6Dmb-0006ZL-DW for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 12:56:12 +0000 Received: by mail-lf1-x130.google.com with SMTP id z21so4974058lfe.1 for ; Fri, 06 Sep 2019 05:56:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=puris-lv.20150623.gappssmtp.com; s=20150623; h=date:from:to:message-id:in-reply-to:references:subject:mime-version; bh=V6+DOJ82zf5Z6Q0aItKGoUm7uR2qz4EOHOqpo8ZcxOU=; b=SUBReDubWSGfwU+eir9w6VnhT6gmGQ0FnDbpDnOFfZSVip84++iIEfLsAKFZdCvfFr bnmRFN/jOVeIVavzgT/dClKKbRg0PFqQSGqd1J/2d4W0FsJxgMqwjbXxCt7YftJdGQMN 6inaZ2HNcoCA/xtngdOYCvoSMZuQ9/wNS7yKYRH8bRFHVBJl4E18zWsKbrOGBZz+46yE IFv2KZQHXkvdV4zG85tsfaEERiGZbm2iwjR8RQeDBH/a17EKNY64doZZjLEIYOKGedmx 8FpnLTiFK2IiH8MDLfHguY9MpwVwpPvhzBcupjZuo7bKx1TIoPsZId/4rNrLE3tBsgLz 0Smg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:message-id:in-reply-to:references :subject:mime-version; bh=V6+DOJ82zf5Z6Q0aItKGoUm7uR2qz4EOHOqpo8ZcxOU=; b=Abwg7p3/mnSoko0pVRBS9eyaKB12uGFdeBt1nitj0MAV9cMH6slZ0HGlFTOKJFGncT 6Vbu52r50qLpGQFEDwXZPJBdJ94Wid4+W0BN9i7svGmG+6ZhrVaBVts+a2Zk0View1hu /0nPuiLuH8V7ngy7Z+rV23s9B4yTrNgQkMEuXoSm6tcwJIWZoGjpcheU/I3E9OmBTGvn hZb0E78y5nIdV0JtSbqiGINyptbz1t/QmWqawijAzxW8pRhD5/mPBcY14H3pADo2pSMo AsichrY2uaU5rksetnHBEGDqlFfAiVrH4EfmW8/1T7ZAlPEOosizOC97aBzQp3tbTQUg mv+A== X-Gm-Message-State: APjAAAVgb54l9AJIve3b38Rw8hNUgBk+UKaK+id9LCUIeDXndf41O43i JxXgSpwDAIw5i/QB4zevrf39DpRga9qFoA== X-Google-Smtp-Source: APXvYqwu1z8lPZHEUle0uU1WXVopwW2EuVem4BjkLmIpO3vG9ZTMw+USbUPqGKA6bg8zO1hb29IJ1g== X-Received: by 2002:a19:cc15:: with SMTP id c21mr6203204lfg.64.1567774568508; Fri, 06 Sep 2019 05:56:08 -0700 (PDT) Received: from [172.25.29.71] (vpn.netwerk.no. [193.58.241.100]) by smtp.gmail.com with ESMTPSA id j5sm1108435lfm.29.2019.09.06.05.56.07 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 06 Sep 2019 05:56:07 -0700 (PDT) Date: Fri, 6 Sep 2019 14:56:02 +0200 From: =?utf-8?Q?J=C4=81nis_P=C5=ABris?= To: pgsql-sql@lists.postgresql.org, jj08 Message-ID: In-Reply-To: <2259317921000000009736706@www> References: <2259317921000000009736706@www> Subject: Re: A complex SQL query X-Readdle-Message-ID: d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="5d725767_643c9869_3c4b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --5d725767_643c9869_3c4b Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Would something like this work for you =3F=C2=A0http://www.sqlfiddle.com/= =23=2117/2e45eb/9 select =C2=A0 =C2=A0 user=5Fid, =C2=A0 =C2=A0 max(start=5Fdate) as start=5Fdate, =C2=A0 =C2=A0 case when max(end=5Fdate) < max(start=5Fdate) then null els= e max(end=5Fdate) end as end=5Fdate from =C2=A0 =C2=A0 employment where =C2=A0 =C2=A0 employer =3D 'Micro' group by =C2=A0 =C2=A0 user=5Fid ; On 6 Sep 2019, 14:31 +0200, jj08 , wrote: > =C2=A0I hope someone can give me some pointers. > Here is my table. > +--------+----------+------------+-----------+ > =7C usr=5Fid =7C employer =7C start=5Fdate =7C end=5Fdate=C2=A0 =7C > +--------+----------+------------+-----------+ > =7C A=C2=A0 =C2=A0 =C2=A0 =7C Goo=C2=A0 =C2=A0 =C2=A0 =7C 201904=C2=A0 = =C2=A0 =C2=A0=7C -=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=7C > =7C A=C2=A0 =C2=A0 =C2=A0 =7C Micro=C2=A0 =C2=A0 =7C 201704=C2=A0 =C2=A0= =C2=A0=7C 201903=C2=A0 =C2=A0 =7C > > =7C B=C2=A0 =C2=A0 =C2=A0 =7C Micro=C2=A0 =C2=A0 =7C 201706=C2=A0 =C2=A0= =C2=A0=7C -=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=7C > =7C B=C2=A0 =C2=A0 =C2=A0 =7C Goo=C2=A0 =C2=A0 =C2=A0 =7C 201012=C2=A0 = =C2=A0 =C2=A0=7C 201705=C2=A0 =C2=A0 =7C > =7C B=C2=A0 =C2=A0 =C2=A0 =7C Micro=C2=A0 =C2=A0 =7C 201001=C2=A0 =C2=A0= =C2=A0=7C 201011=C2=A0 =C2=A0 =7C > +--------+----------+------------+-----------+ > > I am trying to list up people working for a company called =22Micro=22.= > Some people work for a company multiple times, like user B. > I only need one line per user, displaying only the latest affiliation d= ate. > > =46or user=5Fid =22B=22, I could do Select user=5Fid, MAX(start=5Fdate)= , end=5Fdate where employer =3D 'Micro', > but that would fail to get record for user=5Fid =22A=22. > > If multiple records exist, I want to do MAX, but if only a single recor= d exists, I don't need MAX. > How do I do that=3F > =46rom the above data, I would like to see only two lines: > =7C A=C2=A0 =C2=A0 =C2=A0 =7C Micro=C2=A0 =C2=A0 =7C 201704=C2=A0 =C2=A0= =C2=A0=7C 201903=C2=A0 =C2=A0 =7C > =7C B=C2=A0 =C2=A0 =C2=A0 =7C Micro=C2=A0 =C2=A0 =7C 201706=C2=A0 =C2=A0= =C2=A0=7C -=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=7C > Thank you. > > ------------------------- > Online Storage & Sharing, Online Backup, =46TP / Email Server Hosting a= nd More. > Drive Headquarters. Top quality services designed for business=21 > Sign up free at: www.DriveHQ.com. --5d725767_643c9869_3c4b Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

Would something like this work for you =3F&=23160;<= a href=3D=22http://www.sqlfiddle.com/=23=2117/2e45eb/9=22>http://www.sqlf= iddle.com/=23=2117/2e45eb/9

select

&=23160; &=23160; user=5Fid,

&=23160; &=23160; max(start=5Fdate) as start=5Fdate= ,

&=23160; &=23160; case when max(end=5Fdate) < ma= x(start=5Fdate) then null else max(end=5Fdate) end as end=5Fdate

from

&=23160; &=23160; employment

where

&=23160; &=23160; employer =3D 'Micro'

group by

&=23160; &=23160; user=5Fid

;

On 6 Sep 2019, 14:31 +0200, jj08 &l= t;jj08=40drivehq.com>, wrote:

&=23160;I hope someone can give me some pointers.

Here is my table.

+--------+----------+------------+-----------+

=7C usr=5Fid =7C employer =7C start=5Fdate =7C end=5Fdate&=23160; =7C<= /p>
+--------+----------+------------+-----------+

=7C A&=23160; &=23160; &=23160; =7C Goo&=23160; &=23160; &=23160; =7C = 201904&=23160; &=23160; &=23160;=7C -&=23160; &=23160; &=23160; &=23160; = &=23160;=7C

=7C A&=23160; &=23160; &=23160; =7C Micro&=23160; &=23160; =7C 201704&= =23160; &=23160; &=23160;=7C 201903&=23160; &=23160; =7C

&=23160;

=7C B&=23160; &=23160; &=23160; =7C Micro&=23160; &=23160; =7C 201706&= =23160; &=23160; &=23160;=7C -&=23160; &=23160; &=23160; &=23160; &=23160= ;=7C

=7C B&=23160; &=23160; &=23160; =7C Goo&=23160; &=23160; &=23160; =7C = 201012&=23160; &=23160; &=23160;=7C 201705&=23160; &=23160; =7C

=7C B&=23160; &=23160; &=23160; =7C Micro&=23160; &=23160; =7C 201001&= =23160; &=23160; &=23160;=7C 201011&=23160; &=23160; =7C

+--------+----------+------------+-----------+

&=23160;

I am trying to list up people working for a company called =22Micro=22= .

Some people work for a company multiple times, like user B.

I only need one line per user, displaying only the latest affiliation = date.

&=23160;

=46or user=5Fid =22B=22, I could do Select user=5Fid, MAX(start=5Fdate= ), end=5Fdate where employer =3D 'Micro',

but that would fail to get record for user=5Fid =22A=22.

&=23160;

If multiple records exist, I want to do MAX, but if only a single reco= rd exists, I don't need MAX.

How do I do that=3F&=23160;

=46rom the above data, I would like to see only two lines:

=7C A&=23160; &=23160; &=23160; =7C Micro&=23160; &=23160; =7C 201704&= =23160; &=23160; &=23160;=7C 201903&=23160; &=23160; =7C

=7C B&=23160; &=23160; &=23160; =7C Micro&=23160; &=23160; =7C 201706&= =23160; &=23160; &=23160;=7C -&=23160; &=23160; &=23160; &=23160; &=23160= ;=7C

Thank you.

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

--5d725767_643c9869_3c4b-- From jj08@drivehq.com Fri Sep 6 14:35:17 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6FKj-0001hY-4n for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 14:35:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6FKi-000741-0G for pgsql-sql@arkaria.postgresql.org; Fri, 06 Sep 2019 14:35:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6FKh-0006o3-HM for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 14:35:27 +0000 Received: from mail02.mbizgroup.biz ([66.220.9.54] helo=mail.drivehq.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1i6FKb-0004yS-0i for pgsql-sql@lists.postgresql.org; Fri, 06 Sep 2019 14:35:26 +0000 Received: from (192.168.10.210) by EM244o80 with SMTP User ID0; Fri, 06 Sep 2019 14:35:17 GMT Received: from 60.111.238.211 (60.111.238.211) by mail.drivehq.com with HTTPUserID9736706; Fri, 06 Sep 2019 14:35:17 GMT X-Priority: 3 Message-ID: <2259433721000000009736706@www> From: "jj08" To: jj08@drivehq.com, pgsql-sql@lists.postgresql.org, janis@puris.lv Subject: RE:Re: A complex SQL query Date: Fri, 06 Sep 2019 07:35:17 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_225943372_9736706_637033521174417679" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_225943372_9736706_637033521174417679 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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 s= omething 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 from employment where employer =3D 'Micro' group by = user_id ; On 6 Sep 2019, 14:31 +0200, jj08 , wrote: = I hope someone can give me some pointers. Here is my table. +--------+----------+------------+-----------+ | usr_id | employer | start_date | end_date | +--------+----------+------------+-----------+ | A | Goo | 201904 | - | | A | Micro | 201704 | 201903 | = | B | Micro | 201706 | - | | B | Goo | 201012 | 201705 | | B | Micro | 201001 | 201011 | +--------+----------+------------+-----------+ = I am trying to list up people working for a company called "Micro&= quot;. Some people work for a company multiple times, like user B. I only need one line per user, displaying only the latest affiliation d= ate. = For user_id "B", I could do Select user_id, MAX(start_date), = end_date where employer =3D 'Micro', but that would fail to get record for user_id "A". = If multiple records exist, I want to do MAX, but if only a single recor= d exists, I don't need MAX. How do I do that? = From the above data, I would like to see only two lines: | A | Micro | 201704 | 201903 | | B | Micro | 201706 | - | Thank you. = = ------------------------- 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 u= p free at: www.DriveHQ.com . = ------=_NextPart_225943372_9736706_637033521174417679 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Yes, tha= t'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 , wrote:

I hope someone can give me some pointers.

Here is my table.

+--------+----------+------------+-----------+

| usr_id | employer | start_date | end_date |

+--------+----------+------------+-----------+

| A | Goo | 201904 &nbs= p; | - |

| A | Micro | 201704 &n= bsp;| 201903 |

| B | Micro | 201706 &n= bsp;| - |

| B | Goo | 201012 &nbs= p; | 201705 |

| B | Micro | 201001 &n= bsp;| 201011 |

+--------+----------+------------+-----------+

I am trying to list up people working for a company called "Micr= o".

Some people work for a company multiple times, like user B.

I only need one line per user, displaying only the latest affiliation= date.

For user_id "B", I could do Select user_id, MAX(start_date)= , end_date where employer =3D 'Micro',

but that would fail to get record for user_id "A".

If multiple records exist, I want to do MAX, but if only a single rec= ord exists, I don't need MAX.

How do I do that?

From the above data, I would like to see only two lines:

| A | Micro | 201704 &n= bsp;| 201903 |

| B | Micro | 201706 &n= bsp;| - |

Thank you.

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

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

=D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82.= 2019 =D0=B3. =D0=B2 21:23, jj08 <jj= 08@drivehq.com>:

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

+--------+----------+------------+------------------------+
| usr_id | employer | position=C2=A0=C2=A0 + start_date | end_date=C2=A0 |<= br> +--------+----------+------------+------------------------+
| A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | CTO= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201904=C2=A0=C2=A0=C2=A0 |=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
| A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Mgr=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201704=C2=A0=C2=A0=C2=A0=C2=A0 | 201903= =C2=A0=C2=A0=C2=A0 |

| B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Engg_Mgr=C2= =A0=C2=A0 | 201706=C2=A0=C2=A0=C2=A0=C2=A0 | -=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 |
| B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Res= earcher | 201012=C2=A0=C2=A0=C2=A0=C2=A0 | 201705=C2=A0=C2=A0=C2=A0 |
| B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Postdoc=C2=A0= =C2=A0=C2=A0 | 201001=C2=A0=C2=A0=C2=A0=C2=A0 | 201011=C2=A0=C2=A0=C2=A0 |<= br> +--------+----------+------------+------------------------+

SELECT DISTINCT ON (usr_id)
=C2=A0 =C2=A0 =C2=A0= =C2=A0*
=C2=A0 FROM employment
=C2=A0WHERE employer=3D'Micro'= ;
=C2=A0ORDER BY usr_id, start_date DESC;

Victor Ye= gorov

--000000000000e3e6680591fd4e29-- From jj08@drivehq.com Sun Sep 8 02:09:45 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6meN-0001ML-Ji for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 02:09:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6meL-00081q-BI for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 02:09:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6meL-00081i-2k for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 02:09:57 +0000 Received: from mail02.mbizgroup.biz ([66.220.9.54] helo=mail.drivehq.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6meH-0000Lk-9b for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 02:09:56 +0000 Received: from (192.168.10.210) by EM244o80 with SMTP User ID0; Sun, 08 Sep 2019 02:09:48 GMT Received: from 60.111.238.211 (60.111.238.211) by mail.drivehq.com with HTTPUserID9736706; Sun, 08 Sep 2019 02:09:45 GMT X-Priority: 3 Message-ID: <2261310231000000009736706@www> From: "jj08" To: vyegorov@gmail.com, jj08@drivehq.com Cc: pgsql-sql@lists.postgresql.org Subject: RE:Re: RE:Re: A complex SQL query Date: Sat, 07 Sep 2019 19:09:45 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_226131023_9736706_637034801852222136" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_226131023_9736706_637034801852222136 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hello Vicor! It works!!! Thank you very much. = = = = --From: vyegorov@gmail.com --To: jj08@drivehq.com --CC: pgsql-sql@lists.postgresql.org --Date: 9/7/2019 2:33:40 PM --Subject: Re: RE:Re: A complex SQL query = =D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0=B2 21:23, jj0= 8 <jj08@drivehq.com>: = 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 | +--------+-= ---------+------------+------------------------+ = SELECT DISTINCT ON (usr_id) * FROM employment WHERE employer=3D'Micro' ORDER BY usr_id, start_date DESC; = -- Victor Yegorov = = ------------------------- Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and= More. = Drive Headquarters. Top quality services designed for business! Sign u= p free at: www.DriveHQ.com . = ------=_NextPart_226131023_9736706_637034801852222136 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hello Vi= cor!

It works!!!

Thank you very much.

--From: vyegorov@gmail.com
--To: jj08@drivehq.com
--CC: pgsql-sql@lists.postgresql.org
--Date: 9/7/2019 2:33:40 PM
--Subject: Re: RE:Re: A complex SQL query

=D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. = =D0=B2 21:23, jj08 <jj08@drivehq.com= >:

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; |
+--------+----------+------------+------------------------+

SELECT DISTINCT ON (usr_id)
*
FROM employment
WHERE employer=3D'Micro'
ORDER BY usr_id, start_date DESC;

Victor Yegorov

-------------------------
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. =
------=_NextPart_226131023_9736706_637034801852222136-- From v_kalees@yahoo.com Sun Sep 8 03:20:36 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6nlB-0003KH-MZ for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 03:21:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6nl8-0003Q6-II for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 03:21:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6nl7-0003IC-SP for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 03:21:02 +0000 Received: from sonic307-10.consmr.mail.ne1.yahoo.com ([66.163.190.33]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1i6nl5-0006fg-1l for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 03:21:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1567912857; bh=2D7E6ok4/461vG4bSI+mMAMZ9lumHQaQ5S8cqCvSS7o=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=iTZrndATYYL9ItFJoVjnhO76Gf1p8L/er3kApmAZDczDlQ9Rvoh59OXKFE5xzV/NZ/pujpiaGwGA+Fnuo0VkQvGHmZ/BVhJ+uLY/y+ObVlEurOb8qsMOn3CBu7OS5c6XLMrTpB6CdwBGXqh0jM0vNWI6QhfA0HzGJ1jYofKChgjxPzKTO/ym/mSzCPsKqQ4St7rvqL8Hiw6GgaRPo80d/KJ2aYYJKN/WdhjdVEizZoafo4S7b5eq9vLh5dTGUfB3dqOMT7qLycNdyr6ZsHNuWeACBH0o6b8con950jURUoVG9zW84M5DhjpabMuRsnwutCNuxFrF2vwaUOurCcXYNA== X-YMail-OSG: RB8zs2kVM1nwuGa.kVj4LcHcacf8tObtN9F4V2PW5TslFeHV_N09sWMOAMHdV89 ydpppIFKORRNEWFpxFdUAIXYh9n0vOsj5eWgcW756ZA2WdMPTJ_eUs5y2z79qKlkvR2EvHQjDHdL 2RrdGzxdsgU5OCtk26YPqXAHOZZws6EOqKvt33SaqAulwWYdqM4Jzb2AIBLEsztamD71YI.EJBqh 6oLVtwqpWlrktMyIKyUxFTBiRW6MbgVRz07rimW5d9S08MSjFXwH62R0BqWzXuFg7Pgp8DBJlWQ2 cbBD4VMNpBPGpXeDAtrtVVY00ytjyzNmab1wwWxd0F9VmD_Pa.YolXLtHpkxxpyJG3wBwwG5AFc_ mc2iAtG6tO_JO6meYiIyes4HVDjUWP8pQMErYCtVq5mvHW37d4cOEnhyUW3aaSFq521kGUVTnpPm GqZ0HejxAxHckiHm4El.XGFo0riKxBvC3_Urs2JMTQ9NlL4ZL87.AQptF3Yf625y1Rn76wkYBPZz 4mwR1zmpPCRkvCB7ygHbYpXMxHgJXrJwPY6qK.kcDvhL6KaIbglKZaoU5GNSuRPYsZmGE.XXUeVZ kbnkJOm3mul_xhwpdc5QR2zwG_b57cZ9GtPwRD.LlGtglS.814uGBwLyMO42B.GWvqws6k._qn.9 SWtzwc_sJaKu1mVVZsCsd.rtyCS84MlLX53EH1XuwifwVTVGSzuEJCBTMcgtv_2HliOwglKYh.XS 5rWgpefEuUTcOzQRwX3rC638MurfJspG8HP8ylbxVoJC.8kctSx8C7anYfTCgFQNrW8l7e9eTWMe FEzEE9Eq9oj7hXAFhX0cAHwT5ie4hKIR1JftgfEQMRPKh8e88q69yBwy8LPuSO4jkVBSECequYU9 fUjkQO2SsOmjjAYnWsBu5lYIxVF4JxR1Ccriy2MAVlIJsILV1tsnPjHkwDermTiKXBFaSDqlTrRA a4wIhQ935CWGAeFCrqSTD1uugFd4QHk3ec.D.xZ4XaZUt_Gu2M5KI72e8HBKtI6bwJFghZZBLF2f DOmT4V1ZUxyUgLQY0l_8uQ67GR6GUZudm4A_IsHF4SDwIjNckg0duIaiae3VHd2ZGJfhvFxvG44E uXZ3izbV91fU9QwhZNYyiVmQ6km4c8AAOl2Gzu8v16kk_C8d3eWSFK0gra2Pbwh7P_iHuL5GyO_E WjTSsgj1Nru9YYaWr27jCV._BdugpZYkNjaOQ9nBe5ypcgmlrStEID7RJ5RPkh1pg0a6C_4ZjLFQ xy4n506wPDPEVbZi11KP_4IuivghdmYa_ZzKG8AfMNOgxqO9_mvRzoYYAmJ0fRDA.T.Elc7DF17G 7kLI- Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.ne1.yahoo.com with HTTP; Sun, 8 Sep 2019 03:20:57 +0000 Received: by smtp427.mail.ne1.yahoo.com (Oath Hermes SMTP Server) with ESMTPA ID 6d5fe23e7625b271092b42d93826bb19; Sun, 08 Sep 2019 03:20:53 +0000 (UTC) X-YMail-OSG: 4WLln_YVM1nHWZu_6sMF6WIWc7S3hJcDAbJqK3P0mruGpv_TgnDhhvjk9bme6jh P1QPXG8_sF8JqEpwxVz8CeIDQ4zpKzDmbFCAuWHUBx0_wNjjohRFbWAKJ24BXsAMU4clnlTV47UA BmxPlT6jLbjt3UdGIY4KqoH25hOANWDg0xTGggMJpE3WqtT2zJb8aZr6MhQnK_2MSoBSJdH2Qa71 tFw5qWUVG9QF7sIXSCR8bFoiILXR7lnwKo3J41KbDxET7F5u1RuOj3aWZxv3IjrZ6IZOIOMOPZ1g swppncdkPWc8pYB3QEEjyOz8Sa6lf0jR7wbvSVdi0zsDjIHp_si6B.R4sIfwl.uLeW3hCK2NUjCV QCKxLrtrja1M0o3_lKndI.6p0LKNJn5rwfXcQAYewdELOaeFCWe5c0cV623lylGFMGQN6ghozWHU hy0N18Xs.TnO6zMay5YK2xO84bE1bSCdS3CvekWIiJhWvPAhYxM2qyjJ3b4EF8ysDNeh4jaqRNv6 F9nrdxbDQKWOmhj8nVkvqcTuajMr.c6mWeEAau3m_a6hGn1ckjdovQx.bYNQh6MQWCEx4teTt0Qk unHRIWkAuibQfNMCJpgvGw9tN7TwSMgln8oAurK3_4eEySdTkjupWhD19L7hMMqziUKgod1wfR8p lTz3Z9.1ZmrXOAhEXqp21A_aKEosZXxUByb4DacSzBoa13FAs9mVt25HyDcUJh5UXVgzpv1AvZd4 POL8SmkfKjRJ.gGQmdIOi3tsXa6py7IcQvt3Z4PFEE6VPcf8BkDtqqXI0FfaK1gjcNSNunGSIM89 wzf4M4KNoIAXMlb1t5YpSBBQOVvTHYQ3jcw_emYhSYOssPAzbKybpXeNV2oyzBqwAkcLzKLIjNDZ 3mZ.4MmZKaeRtJQX_.SSV3Jp6CCbehat51k5MMxF_8TM6EbW9PvcE.i7Vi_fci5uTg0UhnyuL4m_ CTWyqodZHKQCaHf4PO6Ju5y_LkWlBAFB.72H5UeH.CDbKwFR7S955Yy0MuV6OSTrk14sARJ22S77 QiR0HLbvMG4vZarxT0KWLZMcbIuFeLG.5zJEeoAmc3CWLApkjBldi0IOhvvCd.AzBJEwF7tDm8eQ US2_hU3s3CWyiVd39VldtrAIQJYlzwVUjJu4YVjpYqd4iwZpAnfo_.GbxV4jXdXo48ChUcUmSqIo Tu266z9CQhKm5qX_jjg3FenKl23Sa1ymQllApOwpoWfEXEyW4UoyCmrrfQYSa7wOqBzVh9NleiEP xm6Vtr9K6nFuLdjzKpJLP3Bva2V8WHRD5Om9PUon7fPOx_Av43y7ccZMyQCLlX4ELwNmCd8gRXKo GPyNciJgb7ckO_A45PY39GcSPUus5okVVRlyNkWCRZMG5Fe4OcmjudOCwac.okDBorvvCV.st4I7 WZPGMrkbYwBtxlLx3Loe8No5dcES0gB.r.wTDtpQ- Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Sun, 8 Sep 2019 03:20:53 +0000 Date: Sun, 8 Sep 2019 03:20:36 +0000 (UTC) From: v_kalees Reply-To: v_kalees To: vyegorov@gmail.com, jj08@drivehq.com Cc: pgsql-sql@lists.postgresql.org Message-ID: <1458680600.3892087.1567912836455@mail.yahoo.com> In-Reply-To: <2261310231000000009736706@www> References: <2261310231000000009736706@www> Subject: Re: RE:Re: RE:Re: A complex SQL query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3892086_42522773.1567912836453" X-Mailer: WebService/1.1.14303 YMailNorrin Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko/20100101 Firefox/69.0 Content-Length: 7261 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3892086_42522773.1567912836453 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable This is much cleaner way select * from ( select usr_id,=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 employer,=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 start_date, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 end_date, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 rank() OVER (PARTITION BY employer ORD= ER BY start_date DESC) AS rnk from ) t where t.rnk=3D1; Thanks On Sunday, September 8, 2019, 7:40:05 AM GMT+5:30, jj08 wrote: =20 =20 =20 Hello Vicor! It works!!! Thank you very much. --From: vyegorov@gmail.com --To: jj08@drivehq.com --CC: pgsql-sql@lists.postgresql.org --Date: 9/7/2019 2:33:40 PM --Subject: Re: RE:Re: A complex SQL query =D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0=B2 21:23, jj08 <= jj08@drivehq.com>: 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=C2=A0=C2=A0 + start_date | end_date=C2=A0 | +--------+----------+------------+------------------------+ | A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | CTO= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201904=C2=A0=C2=A0=C2=A0 |=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20 | A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Mgr=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201704=C2=A0=C2=A0=C2=A0=C2=A0 | 201903= =C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Engg_Mgr=C2= =A0=C2=A0 | 201706=C2=A0=C2=A0=C2=A0=C2=A0 | -=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Res= earcher | 201012=C2=A0=C2=A0=C2=A0=C2=A0 | 201705=C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Postdoc=C2=A0= =C2=A0=C2=A0 | 201001=C2=A0=C2=A0=C2=A0=C2=A0 | 201011=C2=A0=C2=A0=C2=A0 | +--------+----------+------------+------------------------+ SELECT DISTINCT ON (usr_id) =C2=A0 =C2=A0 =C2=A0 =C2=A0* =C2=A0 FROM employment =C2=A0WHERE employer=3D'Micro' =C2=A0ORDER BY usr_id, start_date DESC;=C2=A0--=20 Victor Yegorov ------------------------- Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and Mor= e.=20 Drive Headquarters. Top quality services designed for business!=20 Sign up free at: www.DriveHQ.com.=20 =20 ------=_Part_3892086_42522773.1567912836453 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

This is much cleaner way

select * from (
select usr_id,
   = ;    employer,
       star= t_date,
       end_date,
  &n= bsp;    rank() OVER (PARTITION BY employer ORDER BY start_da= te DESC) AS rnk
from <tablename> ) t
where t.rnk=3D1;

Thanks

=20

On Sunday, September 8, 2019, 7:40:05 AM GMT+5:30, jj08= <jj08@drivehq.com> wrote:

Hello Vicor!

It works!!!

Thank you very much.

--From: vyegorov@gmail.com
--To: jj08@drivehq.com
--CC: pgsql-sql@lists.postgresql.org
--Date: 9/7/2019 2:33:40 PM
--Subject: Re: RE:Re: A complex SQL query

=D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0= =B2 21:23, jj08 <jj08@drivehq.com>= ;:

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 |<= br> +--------+----------+------------+------------------------+
| A      | Goo      | CTO=         |     &= nbsp;      | 201904    |  = ;
| A      | Micro    | Mgr &nbs= p;      | 201704     | 201903&= nbsp;   |

| B      | Micro    | Engg_Mgr = ; | 201706     | -     &= nbsp;   |
| B      | Goo      | Res= earcher | 201012     | 201705    |
| B      | Micro    | Postdoc =    | 201001     | 201011    |<= br> +--------+----------+------------+------------------------+

SELECT DISTINCT ON (usr_id)
*
FROM employment
WHERE employer=3D'Micro'
ORDER BY usr_id, start_date DESC;

Victor Yegorov

------=_Part_3892086_42522773.1567912836453-- From jj08@drivehq.com Sun Sep 8 16:12:59 2019 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6zoN-0000wM-Mv for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 16:13:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6zoM-0005DY-Ab for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 16:13:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6zoM-00058w-0i for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 16:13:10 +0000 Received: from mail02.mbizgroup.biz ([66.220.9.54] helo=mail.drivehq.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6zoI-00080s-8n for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 16:13:09 +0000 Received: from (192.168.10.210) by EM244o80 with SMTP User ID0; Sun, 08 Sep 2019 16:13:00 GMT Received: from 126.255.162.228 (126.255.162.228) by mail.drivehq.com with HTTPUserID9736706; Sun, 08 Sep 2019 16:12:59 GMT X-Priority: 3 Message-ID: <2261999271000000009736706@www> From: "jj08" To: v_kalees@yahoo.com, vyegorov@gmail.com Cc: pgsql-sql@lists.postgresql.org Subject: RE:Re: RE:Re: RE:Re: A complex SQL query Date: Sun, 08 Sep 2019 09:12:59 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_226199927_9736706_637035307798840385" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_226199927_9736706_637035307798840385 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable v_kalees: Thank you for your suggestion. I tried your code at http://www.sqlfiddle.com/#!17/4e386/4/1 but couldn't make it work. (I wanted to see two rows, but your query re= turns only one row.) My original question was to find people working for company "Micro= ", so I took liberty to add "where employer=3D'Micro'" co= ndition to your solution. = May be I put the where clause at a wrong place? Anyway, for me it is getting more complex :) Victor's solution is producing 2 rows as I wanted, so for now a crisis = has been averted! = = From: v_kalees@yahoo.com --To: vyegorov@gmail.com,jj08@drivehq.com --CC: pgsql-sql@lists.postgresql.org --Date: 9/7/2019 8:20:57 PM --Subject: Re: RE:Re: RE:Re: A complex SQL = query This is much cleaner way = select * from ( select usr_id, = employer, = start_date, end_date, rank() OVER (PARTITION BY employer ORDER BY start_date DESC) AS = rnk from ) t where t.rnk=3D1; Thanks On Sunday, September 8, 2019, 7:40:05= AM GMT+5:30, jj08 wrote: = Hello Vicor! It works!!! Thank you very much. = = = = --From: vyegorov@gmail.com --To: jj08@drivehq.com --CC: pgsql-sql@lists.postgresql.org --Date: 9/7/2019 2:33:40 PM --Subject: Re: RE:Re: A complex SQL query = =D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0=B2 21:23, jj0= 8 <jj08@drivehq.com>: = 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 | +--------+-= ---------+------------+------------------------+ = SELECT DISTINCT ON (usr_id) * FROM employment WHERE employer=3D'Micro' ORDER BY usr_id, start_date DESC; = -- Victor Yegorov = = ------------------------- Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and= More. = Drive Headquarters. Top quality services designed for business! Sign u= p 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 u= p free at: www.DriveHQ.com . = ------=_NextPart_226199927_9736706_637035307798840385 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

v_kalees= :

Thank you for your suggestion.

I tried your code at http://www.sqlfiddle.com/#!17/4e386/4/1

but couldn't make it work. (I wanted to see two rows, but your query = returns only one row.)

My original question was to find people working for company "Mic= ro", so I took liberty to add "where employer=3D'Micro'" = condition to your solution.

May be I put the where clause at a wrong place?

Anyway, for me it is getting more complex :)

Victor's solution is producing 2 rows as I wanted, so for now a crisi= s has been averted!

From: v_kalees@yahoo.com
--To: vyegorov@gmail.com,jj08@drivehq.com
--CC: pgsql-sql@lists.postgresql.org
--Date: 9/7/2019 8:20:57 PM
--Subject: Re: RE:Re: RE:Re: A complex SQL query

This is much cleaner way

select * from (
select usr_id,
       employer,
       start_date,
       end_date,
       rank() OVER (PARTITION BY employer = ORDER BY start_date DESC) AS rnk
from ) t
where t.rnk=3D1;

Thanks

On Sunday, September 8, 2019, 7:40:05 AM GMT+5:30, jj08 wrote:

Hello Vicor!

It works!!!

Thank you very much.

--From: vyegorov@gmail.com
--To: jj08@drivehq.com
--CC: pgsql-sql@lists.postgresql.org
--Date: 9/7/2019 2:33:40 PM
--Subject: Re: RE:Re: A complex SQL query

=D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. = =D0=B2 21:23, jj08 <jj08@drivehq.com<= /a>>:

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; |
+--------+----------+------------+------------------------+

SELECT DISTINCT ON (usr_id)
*
FROM employment
WHERE employer=3D'Micro'
ORDER BY usr_id, start_date DESC;

Victor Yegorov