agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Jānis Pūris <janis@puris.lv>
To: pgsql-sql@lists.postgresql.org, jj08 <jj08@drivehq.com>
Subject: Re: A complex SQL query
Date: Fri, 6 Sep 2019 14:56:02 +0200
Message-ID: <d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark> (raw)
In-Reply-To: <2259317921000000009736706@www>
References: <2259317921000000009736706@www>
Would something 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 = 'Micro'
group by
user_id
;
On 6 Sep 2019, 14:31 +0200, jj08 <jj08@drivehq.com>, 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".
> 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.
view thread (8+ messages) latest in thread
Message-ID: <d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark>
Permalink: ../d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark/
Also on: postgresql.org/message-id/d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janis@puris.lv, jj08@drivehq.com
Subject: Re: A complex SQL query
In-Reply-To: <d1ed2c23-e5ea-4eab-adc9-75ec12ec7038@Spark>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox