How to show only the latest record in SQL

Question

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

Use `TOP` and `ORDER BY` (there is also no need for the `DISTONCT` then). — Thom A, Oct 09 '21 at 09:26
Note: that's just sample data out of huge data. There are many other data, just took ID = 5580 as an example only — Mezameta Yasuo, Oct 09 '21 at 10:58
Just order by date desc and id desc then limit result to 1 row; — Ptit Xav, Oct 09 '21 at 17:07
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Oct 09 '21 at 19:56

score 0 · Answer 1 · edited Oct 09 '21 at 11:09

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

How create new table when you just want to select last line ? — Elikill58, Oct 09 '21 at 10:37

score 0 · Answer 2 · answered Oct 09 '21 at 12:45

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

How to show only the latest record in SQL

2 Answers2