From: | v_kalees <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 |
Subject: | Re: RE:Re: RE:Re: A complex SQL query |
Date: | 2019-09-08 03:20:36 |
Message-ID: | 1458680600.3892087.1567912836455@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 <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 "position".
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Karen Goh | 2019-09-08 04:11:51 | Re: How do I enabled Windows 10 to be able to run PSQL etc |
Previous Message | jj08 | 2019-09-08 02:09:45 | RE:Re: RE:Re: A complex SQL query |