Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6nlB-0003KH-MZ for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 03:21:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i6nl8-0003Q6-II for pgsql-sql@arkaria.postgresql.org; Sun, 08 Sep 2019 03:21:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i6nl7-0003IC-SP for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 03:21:02 +0000 Received: from sonic307-10.consmr.mail.ne1.yahoo.com ([66.163.190.33]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1i6nl5-0006fg-1l for pgsql-sql@lists.postgresql.org; Sun, 08 Sep 2019 03:21:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1567912857; bh=2D7E6ok4/461vG4bSI+mMAMZ9lumHQaQ5S8cqCvSS7o=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=iTZrndATYYL9ItFJoVjnhO76Gf1p8L/er3kApmAZDczDlQ9Rvoh59OXKFE5xzV/NZ/pujpiaGwGA+Fnuo0VkQvGHmZ/BVhJ+uLY/y+ObVlEurOb8qsMOn3CBu7OS5c6XLMrTpB6CdwBGXqh0jM0vNWI6QhfA0HzGJ1jYofKChgjxPzKTO/ym/mSzCPsKqQ4St7rvqL8Hiw6GgaRPo80d/KJ2aYYJKN/WdhjdVEizZoafo4S7b5eq9vLh5dTGUfB3dqOMT7qLycNdyr6ZsHNuWeACBH0o6b8con950jURUoVG9zW84M5DhjpabMuRsnwutCNuxFrF2vwaUOurCcXYNA== X-YMail-OSG: RB8zs2kVM1nwuGa.kVj4LcHcacf8tObtN9F4V2PW5TslFeHV_N09sWMOAMHdV89 ydpppIFKORRNEWFpxFdUAIXYh9n0vOsj5eWgcW756ZA2WdMPTJ_eUs5y2z79qKlkvR2EvHQjDHdL 2RrdGzxdsgU5OCtk26YPqXAHOZZws6EOqKvt33SaqAulwWYdqM4Jzb2AIBLEsztamD71YI.EJBqh 6oLVtwqpWlrktMyIKyUxFTBiRW6MbgVRz07rimW5d9S08MSjFXwH62R0BqWzXuFg7Pgp8DBJlWQ2 cbBD4VMNpBPGpXeDAtrtVVY00ytjyzNmab1wwWxd0F9VmD_Pa.YolXLtHpkxxpyJG3wBwwG5AFc_ mc2iAtG6tO_JO6meYiIyes4HVDjUWP8pQMErYCtVq5mvHW37d4cOEnhyUW3aaSFq521kGUVTnpPm GqZ0HejxAxHckiHm4El.XGFo0riKxBvC3_Urs2JMTQ9NlL4ZL87.AQptF3Yf625y1Rn76wkYBPZz 4mwR1zmpPCRkvCB7ygHbYpXMxHgJXrJwPY6qK.kcDvhL6KaIbglKZaoU5GNSuRPYsZmGE.XXUeVZ kbnkJOm3mul_xhwpdc5QR2zwG_b57cZ9GtPwRD.LlGtglS.814uGBwLyMO42B.GWvqws6k._qn.9 SWtzwc_sJaKu1mVVZsCsd.rtyCS84MlLX53EH1XuwifwVTVGSzuEJCBTMcgtv_2HliOwglKYh.XS 5rWgpefEuUTcOzQRwX3rC638MurfJspG8HP8ylbxVoJC.8kctSx8C7anYfTCgFQNrW8l7e9eTWMe FEzEE9Eq9oj7hXAFhX0cAHwT5ie4hKIR1JftgfEQMRPKh8e88q69yBwy8LPuSO4jkVBSECequYU9 fUjkQO2SsOmjjAYnWsBu5lYIxVF4JxR1Ccriy2MAVlIJsILV1tsnPjHkwDermTiKXBFaSDqlTrRA a4wIhQ935CWGAeFCrqSTD1uugFd4QHk3ec.D.xZ4XaZUt_Gu2M5KI72e8HBKtI6bwJFghZZBLF2f DOmT4V1ZUxyUgLQY0l_8uQ67GR6GUZudm4A_IsHF4SDwIjNckg0duIaiae3VHd2ZGJfhvFxvG44E uXZ3izbV91fU9QwhZNYyiVmQ6km4c8AAOl2Gzu8v16kk_C8d3eWSFK0gra2Pbwh7P_iHuL5GyO_E WjTSsgj1Nru9YYaWr27jCV._BdugpZYkNjaOQ9nBe5ypcgmlrStEID7RJ5RPkh1pg0a6C_4ZjLFQ xy4n506wPDPEVbZi11KP_4IuivghdmYa_ZzKG8AfMNOgxqO9_mvRzoYYAmJ0fRDA.T.Elc7DF17G 7kLI- Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.ne1.yahoo.com with HTTP; Sun, 8 Sep 2019 03:20:57 +0000 Received: by smtp427.mail.ne1.yahoo.com (Oath Hermes SMTP Server) with ESMTPA ID 6d5fe23e7625b271092b42d93826bb19; Sun, 08 Sep 2019 03:20:53 +0000 (UTC) X-YMail-OSG: 4WLln_YVM1nHWZu_6sMF6WIWc7S3hJcDAbJqK3P0mruGpv_TgnDhhvjk9bme6jh P1QPXG8_sF8JqEpwxVz8CeIDQ4zpKzDmbFCAuWHUBx0_wNjjohRFbWAKJ24BXsAMU4clnlTV47UA BmxPlT6jLbjt3UdGIY4KqoH25hOANWDg0xTGggMJpE3WqtT2zJb8aZr6MhQnK_2MSoBSJdH2Qa71 tFw5qWUVG9QF7sIXSCR8bFoiILXR7lnwKo3J41KbDxET7F5u1RuOj3aWZxv3IjrZ6IZOIOMOPZ1g swppncdkPWc8pYB3QEEjyOz8Sa6lf0jR7wbvSVdi0zsDjIHp_si6B.R4sIfwl.uLeW3hCK2NUjCV QCKxLrtrja1M0o3_lKndI.6p0LKNJn5rwfXcQAYewdELOaeFCWe5c0cV623lylGFMGQN6ghozWHU hy0N18Xs.TnO6zMay5YK2xO84bE1bSCdS3CvekWIiJhWvPAhYxM2qyjJ3b4EF8ysDNeh4jaqRNv6 F9nrdxbDQKWOmhj8nVkvqcTuajMr.c6mWeEAau3m_a6hGn1ckjdovQx.bYNQh6MQWCEx4teTt0Qk unHRIWkAuibQfNMCJpgvGw9tN7TwSMgln8oAurK3_4eEySdTkjupWhD19L7hMMqziUKgod1wfR8p lTz3Z9.1ZmrXOAhEXqp21A_aKEosZXxUByb4DacSzBoa13FAs9mVt25HyDcUJh5UXVgzpv1AvZd4 POL8SmkfKjRJ.gGQmdIOi3tsXa6py7IcQvt3Z4PFEE6VPcf8BkDtqqXI0FfaK1gjcNSNunGSIM89 wzf4M4KNoIAXMlb1t5YpSBBQOVvTHYQ3jcw_emYhSYOssPAzbKybpXeNV2oyzBqwAkcLzKLIjNDZ 3mZ.4MmZKaeRtJQX_.SSV3Jp6CCbehat51k5MMxF_8TM6EbW9PvcE.i7Vi_fci5uTg0UhnyuL4m_ CTWyqodZHKQCaHf4PO6Ju5y_LkWlBAFB.72H5UeH.CDbKwFR7S955Yy0MuV6OSTrk14sARJ22S77 QiR0HLbvMG4vZarxT0KWLZMcbIuFeLG.5zJEeoAmc3CWLApkjBldi0IOhvvCd.AzBJEwF7tDm8eQ US2_hU3s3CWyiVd39VldtrAIQJYlzwVUjJu4YVjpYqd4iwZpAnfo_.GbxV4jXdXo48ChUcUmSqIo Tu266z9CQhKm5qX_jjg3FenKl23Sa1ymQllApOwpoWfEXEyW4UoyCmrrfQYSa7wOqBzVh9NleiEP xm6Vtr9K6nFuLdjzKpJLP3Bva2V8WHRD5Om9PUon7fPOx_Av43y7ccZMyQCLlX4ELwNmCd8gRXKo GPyNciJgb7ckO_A45PY39GcSPUus5okVVRlyNkWCRZMG5Fe4OcmjudOCwac.okDBorvvCV.st4I7 WZPGMrkbYwBtxlLx3Loe8No5dcES0gB.r.wTDtpQ- Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Sun, 8 Sep 2019 03:20:53 +0000 Date: Sun, 8 Sep 2019 03:20:36 +0000 (UTC) From: v_kalees Reply-To: v_kalees To: vyegorov@gmail.com, jj08@drivehq.com Cc: pgsql-sql@lists.postgresql.org Message-ID: <1458680600.3892087.1567912836455@mail.yahoo.com> In-Reply-To: <2261310231000000009736706@www> References: <2261310231000000009736706@www> Subject: Re: RE:Re: RE:Re: A complex SQL query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3892086_42522773.1567912836453" X-Mailer: WebService/1.1.14303 YMailNorrin Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko/20100101 Firefox/69.0 Content-Length: 7261 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3892086_42522773.1567912836453 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable This is much cleaner way select * from ( select usr_id,=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 employer,=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 start_date, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 end_date, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 rank() OVER (PARTITION BY employer ORD= ER BY start_date DESC) AS rnk from ) t where t.rnk=3D1; Thanks On Sunday, September 8, 2019, 7:40:05 AM GMT+5:30, jj08 wrote: =20 =20 =20 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 =D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0=B2 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=C2=A0=C2=A0 + start_date | end_date=C2=A0 | +--------+----------+------------+------------------------+ | A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | CTO= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201904=C2=A0=C2=A0=C2=A0 |=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20 | A=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Mgr=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 201704=C2=A0=C2=A0=C2=A0=C2=A0 | 201903= =C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Engg_Mgr=C2= =A0=C2=A0 | 201706=C2=A0=C2=A0=C2=A0=C2=A0 | -=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Goo=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Res= earcher | 201012=C2=A0=C2=A0=C2=A0=C2=A0 | 201705=C2=A0=C2=A0=C2=A0 | | B=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | Micro=C2=A0=C2=A0=C2=A0 | Postdoc=C2=A0= =C2=A0=C2=A0 | 201001=C2=A0=C2=A0=C2=A0=C2=A0 | 201011=C2=A0=C2=A0=C2=A0 | +--------+----------+------------+------------------------+ SELECT DISTINCT ON (usr_id) =C2=A0 =C2=A0 =C2=A0 =C2=A0* =C2=A0 FROM employment =C2=A0WHERE employer=3D'Micro' =C2=A0ORDER BY usr_id, start_date DESC;=C2=A0--=20 Victor Yegorov ------------------------- Online Storage & Sharing, Online Backup, FTP / Email Server Hosting and Mor= e.=20 Drive Headquarters. Top quality services designed for business!=20 Sign up free at: www.DriveHQ.com.=20 =20 ------=_Part_3892086_42522773.1567912836453 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
This is much cleaner way

select * from (
select usr_id,
   = ;    employer,
       star= t_date,
       end_date,
  &n= bsp;    rank() OVER (PARTITION BY employer ORDER BY start_da= te DESC) AS rnk
from <tablename> ) t
where t.rnk=3D1;

Thanks
=20
=20
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

=D1=81=D0=B1, 7 =D1=81=D0=B5=D0=BD=D1=82. 2019 =D0=B3. =D0= =B2 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  |<= br> +--------+----------+------------+------------------------+
| A      | Goo      | CTO=         |     &= nbsp;      | 201904    |  = ;      
| A      | Micro    | Mgr &nbs= p;      | 201704     | 201903&= nbsp;   |

| B      | Micro    | Engg_Mgr = ;  | 201706     | -     &= nbsp;   |
| B      | Goo      | Res= earcher | 201012     | 201705    |
| B      | Micro    | Postdoc =    | 201001     | 201011    |<= br> +--------+----------+------------+------------------------+

SELECT DISTINCT ON (usr_id)
       *
  FROM employment
 WHERE employer=3D'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.co= m.=20
------=_Part_3892086_42522773.1567912836453--