How to show only the latest record in SQL-CodePudding

I have this issue where I want to show only the latest record (Col 1). I deleted the date column thinking that it might not work if it has different values. but if that's the case, then the record itself has a different name (Col 1) because it has a different date in the name of it. Is it possible to fetch one record in this case?

The code:

SELECT distinct p.ID, 
  max(at.Date) as date, 
  at.[RAPID3 Name] as COL1, 
  at.[DLQI Name] AS COL2, 
  at.[HAQ-DI Name] AS COL3,  
  phy.name as phyi, 
  at.State_ID
FROM dbo.[Assessment Tool] as at
Inner join dbo.patient as p on p.[ID] = at.[Owner (Patient)_Patient_ID]
Inner join dbo.[Physician] as phy on phy.ID = p.Physician_ID
where (at.State_ID in (162, 165,168) and p.ID = 5580) 
group by 
at.[RAPID3 Name], 
at.[DLQI Name], 
at.[HAQ-DI Name], 
p.ID, phy.name, 
at.State_ID

SS:

In this SS I want to show only the latest record (COL 1) of this ID "5580". Means the first row for this ID.

Thank you

CodePudding user response：

The Most Accurate way to handle this.

Extract The Date.
Than use Top and Order.

    create table #Temp(
        ID int, 
        Col1 Varchar(50) null, 
        Col2 Varchar(50) null, 
        Col3 Varchar(50) null, 
        Phyi Varchar(50) null,
        State_ID int)
    
    Insert Into #Temp values(5580,'[9/29/2021]-[9.0]High Severity',null,null,'Eman Elshorpagy',168)
    Insert Into #Temp values(5580,'[10/3/2021]-[9.3]High Severity',null,null,'Eman Elshorpagy',168)
    
   

     select top 1 * from #Temp as t
        order by cast((Select REPLACE((SELECT REPLACE((SELECT top 1 Value FROM STRING_SPLIT(t.Col1,'-')),'[','')),']','')) as date) desc

CodePudding user response：

This is close to ANSI standard, and it also caters for the newest row per id.

The principle is to use ROW_NUMBER() using a descending order on the date/timestamp (using a DATE type instead of a DATETIME and avoiding the keyword DATE for a column name) in one query, then to select from that query using the result of row number for the filter.

-- your input, but 2 id-s to show how it works with many ..
indata(id,dt,col1,phyi,state_id) AS (
            SELECT 5580,DATE '2021-10-03','[10/3/2021] - [9,3] High Severity','Eman Elshorpagy',168
  UNION ALL SELECT 5580,DATE '2021-09-29','[9/29/2021] - [9,0] High Severity','Eman Elshorpagy',168 
  UNION ALL SELECT 5581,DATE '2021-10-03','[10/3/2021] - [9,3] High Severity','Eman Elshorpagy',168 
  UNION ALL SELECT 5581,DATE '2021-09-29','[9/29/2021] - [9,0] High Severity','Eman Elshorpagy',168 
)
-- real query starts here, replace following comman with "WITH" ...
,
with_rank AS (
  SELECT
    *   
  , ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt DESC) AS rank_id
  FROM indata
)
SELECT
  id  
, dt
, col1
, phyi
, state_id
FROM with_rank
WHERE rank_id=1
;
  id  |     dt     |               col1                |      phyi       | state_id 
------ ------------ ----------------------------------- ----------------- ----------
 5580 | 2021-10-03 | [10/3/2021] - [9,3] High Severity | Eman Elshorpagy |      168
 5581 | 2021-10-03 | [10/3/2021] - [9,3] High Severity | Eman Elshorpagy |      168