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