From: | "jj08" <jj08(at)drivehq(dot)com> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | A complex SQL query |
Date: | 2019-09-06 12:31:28 |
Message-ID: | 2259317921000000009736706@www |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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".
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 = '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 record 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
.
From | Date | Subject | |
---|---|---|---|
Next Message | Jānis Pūris | 2019-09-06 12:56:02 | Re: A complex SQL query |
Previous Message | Tom Lane | 2019-09-05 13:46:30 | Re: Question about WHERE CASE |