RE:Re: RE:Re: RE:Re: A complex SQL query

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 "Micro", so I took liberty to add "where employer='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 crisis has been averted!

From: v_kalees(at)yahoo(dot)com
--To: vyegorov(at)gmail(dot)com,jj08(at)drivehq(dot)com
--CC: pgsql-sql(at)lists(dot)postgresql(dot)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 <tablename>
) t where t.rnk=1; Thanks On Sunday, September 8, 2019, 7:40:05 AM GMT+5:30, jj08 <jj08(at)drivehq(dot)com> wrote:

Hello Vicor!

It works!!!

Thank you very much.

--From: vyegorov(at)gmail(dot)com
--To: jj08(at)drivehq(dot)com
--CC: pgsql-sql(at)lists(dot)postgresql(dot)org
--Date: 9/7/2019 2:33:40 PM --Subject: Re: RE:Re: A complex SQL query сб, 7 сент. 2019 г. в 21:23, jj08 <<a rel="nofollow" ymailto="mailto:jj08(at)drivehq(dot)com" target="_blank" href="mailto:jj08(at)drivehq(dot)com">jj08(at)drivehq(dot)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 &quot;position&quot;.
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='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.

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