how to get separate value from same column on Oracle SQL

Question

apartment_Table

ID	update_Date	code	address1	address2	city	state_code	zip_code
1	2023/02/15	CO	Apt 320	null	NYC	NY	10012
1	2021/12/03	CO	Apt 105	null	NYC	NY	10012
1	2023/02/15	WK	5th Avenue	null	NYC	NY	10012
2	2023/02/15	CO	Apt 325	null	NYC	NY	10012
2	2022/01/12	CO	Apt 123	null	NYC	NY	10012
2	2023/02/14	WK	4th Avenue	null	NYC	NY	10012
2	2021/02/11	WK	5th Avenue	null	NYC	NY	10012

From the table, WK code is road_name and CO code is apartment number.

update_Date is the latest data when user update in the system. I would like to know How do I get result like below

(latest update_Date WK belong to latest update_Date CO)

ID	tenant_address
1	5th Avenue, Apt 320, NYC, NY, 10012
2	4th Avenue, Apt 325, NYC, NY, 10012

SQL I wrote:

SELECT
View_B_ID AS ViewID,
DECODE(P.address1, NULL, 'Street Not Available, ', SUBSTR(P.address1, 1, 50) || ', ')
|| DECODE(P.address2, NULL, '', SUBSTR(P.address2, 1, 50) || ', ')
|| DECODE(P.CITY, NULL, '', P.CITY || ', ')
|| DECODE(P.STAT_CODE, NULL, '', STAT_CODE || ', ')
|| DECODE(P.ZIP, NULL, '', P.ZIP) AS tenant_address
FROM View_B --assume I get data from View_B
 LEFT JOIN (SELECT R.ID,
            R.address1,
            R.address2,
            R.CITY,
            R.STAT_CODE,
            R.ZIP,
            ROW_NUMBER() OVER(PARTITION BY R.ID ORDER BY R.update_Date DESC) AS RN
            FROM apartment_Table R
            WHERE R.code = 'CO'
            ) P
ON ViewID = P.ID
AND P.RN = 1

Output:

ID	tenant_address
1	Apt 320, NYC, NY, 10012
2	Apt 325, NYC, NY, 10012

Should I use LISTAGG function ? how to get the correct result ? please help. thank you so much

How do you identify the COs which belong to a WK? eg id2 has 2 WKs in the published sample. — P.Salmon, Feb 16 '23 at 08:35
@P.Salmon this is a good question. I think I forgot to mention we need to look at update_Date column to see which data is the latest. so the latest WK belong to the latest CO — Lyon, Feb 16 '23 at 08:41
Does this answer your question? [GROUP BY with MAX(DATE)](https://stackoverflow.com/questions/3491329/group-by-with-maxdate) — astentx, Feb 16 '23 at 09:18

Mahamoutou · Answer 1 · 2023-02-16T17:16:06.220

You could use max keep dense_rank aggregate function to get the job done as below :

SELECT ID,
       REGEXP_REPLACE(MAX(ADDRESS1) /* WK code road_name */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS1) /* CO code apartment number */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'CO', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS2)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(CITY)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(STATE_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ZIP_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC)
                 , '(,[[:space:]])(,)'
                 , '\2'
              ) AS TENANT_ADDRESS
  FROM YOUR_TABLE_NAME T
 GROUP BY ID;

demo on db<>fiddle

See oracle documentation for more details

Hi Maham, This is super helpful ! May I know how will you suggest to find this specific function without any experience on that, because it really difficult to find a direction, Is there any useful website for SQL you can recommend please ? — Lyon, Feb 16 '23 at 16:22
@Lyon, I added a link to the oracle documentation section that explains KEEP DENSE_RANK clause — Mahamoutou, Feb 16 '23 at 17:17

score 0 · Answer 2 · answered Feb 16 '23 at 19:37

You could create a query (tmax) with max dates per ID for both codes 'WK' and 'CO' having them in the same row. Then join your table twice by codes and just concat your tenant's latest address:

SELECT      tmax.ID, 
            twk.ADDRESS1 || ', ' || tco.ADDRESS1 || ', ' || tco.CITY || ', ' || tco.STATE_CODE || ', ' || tco.ZIP_CODE "TENANT_ADDRESS"
FROM        ( Select      ID, 
                          MAX(CASE WHEN CODE = 'WK' THEN UPD_DATE END) "WK_DATE",
                          MAX(CASE WHEN CODE = 'CO' THEN UPD_DATE END) "CO_DATE" 
              From        tbl
              Group By    ID
            ) tmax
INNER JOIN  tbl twk ON(twk.ID = tmax.ID And twk.UPD_DATE = tmax.WK_DATE and twk.CODE = 'WK')
INNER JOIN  tbl tco ON(tco.ID = tmax.ID And tco.UPD_DATE = tmax.CO_DATE and tco.CODE = 'CO')

With your sample data:

WITH
    tbl (ID,    UPD_DATE,   CODE,   ADDRESS1,   ADDRESS2,   CITY,   STATE_CODE, ZIP_CODE) AS
        (
            Select 1, To_Date('2023/02/15', 'yyyy/mm/dd'), 'CO', 'Apt 320',     Null, 'NYC', 'NY', 10012 From Dual Union All 
            Select 1, To_Date('2021/12/03', 'yyyy/mm/dd'), 'CO', 'Apt 105',     Null, 'NYC', 'NY', 10012 From Dual Union All
            Select 1, To_Date('2023/02/15', 'yyyy/mm/dd'), 'WK', '5th Avenue',  Null, 'NYC', 'NY', 10012 From Dual Union All
            Select 2, To_Date('2023/02/15', 'yyyy/mm/dd'), 'CO', 'Apt 325',     Null, 'NYC', 'NY', 10012 From Dual Union All
            Select 2, To_Date('2022/01/12', 'yyyy/mm/dd'), 'CO', 'Apt 123',     Null, 'NYC', 'NY', 10012 From Dual Union All
            Select 2, To_Date('2023/02/14', 'yyyy/mm/dd'), 'WK', '4th Avenue',  Null, 'NYC', 'NY', 10012 From Dual Union All
            Select 2, To_Date('2021/02/11', 'yyyy/mm/dd'), 'WK', '5th Avenue',  Null, 'NYC', 'NY', 10012 From Dual 
        )

... you should get this as the result


        ID TENANT_ADDRESS                        
---------- --------------------------------------
         1 5th Avenue, Apt 320, NYC, NY, 10012   
         2 4th Avenue, Apt 325, NYC, NY, 10012

how to get separate value from same column on Oracle SQL

2 Answers2