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.

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

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