Get only lates t dates for a subset of ta table. Want to structure the SQL query better, avoid using-CodePudding

How to write this SQL query in a better way. Can I avoid using a CTE?

For a subset of table_1 I need to get only the data with the latest integration_date. For data not in the subset (variable_A != 'X') I want all data from table_1.

Latest_dates AS ( SELECT MAX(INTEGRATION_DATE) AS MAX_INTEGRATION_DATE, ID FROM table_1 GROUP BY ID) 
    SELECT S.* FROM Table_1 AS S 
                LEFT JOIN Latest_dates ON S.INTEGRATION_DATE = Latest_dates.MAX_INTEGRATION_DATE AND S.ID= Latest_dates.ID 
                WHERE Latest_dates.MAX_INTEGRATION_DATE is not NULL 
                OR S.variable_A != 'X'

Code works fine, but looks ugly.

CodePudding user response：

You don't need to avoid the CTE but you can use ranking functions:

WITH CTE AS
(
    SELECT ID, INTEGRATION_DATE, 
           IdDateRank = RANK() OVER (PARTITION BY ID ORDER BY INTEGRATION_DATE DESC)
          -- other columns
    WHERE S.variable_A != 'X'
)
SELECT ID, INTEGRATION_DATE -- other columns
FROM CTE WHERE IdDateRank = 1

CodePudding user response：

select top 1 * with ties 
from Table_1 
order by row_number() over (partition by id order by case when variable_A = 'X' then null else INTEGRATION_DATE end desc) asc