0

I want to query the table below and retrieve the latest record (based on ISSUE_DATE) for each ID that way I can get the latest information. The problem is that one ID may have more than 1 records with different T_START_DATE and T_END_DATE / START_DATE and END_DATE, but of course I want to have MIN(T_START_DATE) and MAX(T_END_DATE) / MIN(START_DATE) and MAX(END_DATE). Here is a sample dataset I am working with:

ID        SA_NO ISSUE_DATE  START_DATE  END_DATE    T_START_DATE    T_END_DATE
378_1A_A_20 4   04/09/2019                           08/09/2019     31/12/2019
378_1A_A_20 3   17/07/2019                           21/07/2019     30/09/2019
378_1A_A_20 2   09/07/2019                           13/07/2019     30/09/2019
378_1A_A_20 1   26/06/2019                           30/06/2019     30/09/2019
378_1A_A_5  3   02/07/2019                           08/09/2019     31/12/2019
378_1A_A_6  4   04/09/2019                           08/09/2019     31/12/2019
378_1A_A_G  4   04/09/2019  08/09/2019  31/12/2019      
378_1A_A_G  3   17/07/2019  21/07/2019  30/09/2019      
378_1A_A_G  2   09/07/2019  13/07/2019  30/09/2019      
378_1A_A_G  1   26/06/2019  30/06/2019  30/09/2019      
378_1A_B_20 4   04/09/2019                           01/01/2019     31/10/2019
378_1A_B_20 4   04/09/2019                           01/11/2019     31/12/2019

I would expect to get the results like this:

ID        SA_NO ISSUE_DATE  START_DATE  END_DATE    T_START_DATE    T_END_DATE
378_1A_A_20 4   04/09/2019                           08/09/2019     31/12/2019
378_1A_A_5  3   02/07/2019                           08/09/2019     31/12/2019
378_1A_A_6  4   04/09/2019                           08/09/2019     31/12/2019
378_1A_A_G  4   04/09/2019  08/09/2019  31/12/2019      
378_1A_B_20 4   04/09/2019                           01/01/2019     31/12/2019

Any comments/corrections would be much appreciated, thanks!

MT0
  • 143,790
  • 11
  • 59
  • 117
Nelson Lau
  • 1
  • 3
  • Welcome to SO, I know this is a duplicate but some general advice: When asking a question, try make the question as clear and easy to understand as possible. A general rule of thumb: if you are proof reading your question and it takes more than one attempt to make sure the question is coherent I advise changing it. Personally, I found everything from your second sentence to the sample table difficult to understand. – Kevin Sep 17 '19 at 09:00

2 Answers2

0

use row_number()

select * from (select *,row_number() over(partition by id order by issue_date desc) rn
from table_name
) a where a.rn=1
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

You need to use analytical functions as following:

SELECT ID, SA_NO, ISSUE_DATE, START_DATE, END_DATE, 
T_START_DATE_MIN AS T_START_DATE, T_END_DATE_MAX AS T_END_DATE 
FROM
(select ID, SA_NO, ISSUE_DATE, START_DATE, END_DATE, T_START_DATE, T_END_DATE, 
MIN(T_START_DATE) OVER (PARTITION BY ID) AS  T_START_DATE_MIN,
MAX(T_END_DATE) OVER (PARTITION BY ID) AS  T_END_DATE_MAX,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ISSUE_DATE DESC) AS  RN,
FROM YOUR_TABLE )
WHERE RN = 1

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31