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

Поиск
Список
Период
Сортировка
От v_kalees
Тема Re: RE:Re: RE:Re: A complex SQL query
Дата
Msg-id 1458680600.3892087.1567912836455@mail.yahoo.com
обсуждение исходный текст
Ответ на RE:Re: RE:Re: A complex SQL query  ("jj08" <jj08@drivehq.com>)
Список 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@drivehq.com> wrote:


Hello Vicor!

It works!!!

Thank you very much.





--From: vyegorov@gmail.com
--To: jj08@drivehq.com
--CC: pgsql-sql@lists.postgresql.org
--Date: 9/7/2019 2:33:40 PM
--Subject: Re: RE:Re: A complex SQL query

сб, 7 сент. 2019 г. в 21:23, jj08 <jj08@drivehq.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.

В списке pgsql-sql по дате отправления: