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: [email protected]
 --To: [email protected],[email protected]
 --CC: [email protected]
 --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 <[email protected]> wrote:        

 Hello Vicor!

 It works!!!

 Thank you very much.

 

 
 
 
 --From: [email protected]
 --To: [email protected]
 --CC: [email protected]
 --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:[email protected]" target="_blank" href="mailto:[email protected]">[email protected]>:   

 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
.