Where clause - MAX Date

Question

Hi,

I have the following code below which produces 18 records, all have different criteria associated, but the difference between all rows is the date.

What I want to see if the 18 records into 1 record by only showing the record with the latest date only.

Any idea how to add this into my query please?

select distinct
a.TY_REF,
a.TY_DESC,
a.TY_POS,
a.TY_STA_POS,
b.PT_DATE_START,
b.PT_id,

from [ty_dwh].[dbo].[ty_supps] as a
inner join [ty_dwh].[dbo].[pt_supp] as b
on a.RS_REF = b.RS_REF

Answer 1

top 1 with descending order by date will be enough?

select distinct top 1
a.TY_REF,
a.TY_DESC,
a.TY_POS,
a.TY_STA_POS,
b.PT_DATE_START,
b.PT_id,

from [ty_dwh].[dbo].[ty_supps] as a
inner join [ty_dwh].[dbo].[pt_supp] as b
on a.RS_REF = b.RS_REF
order by b.PT_DATE_START desc

Answer 2

Hi @Bone_12 ,

The where clause and the maximum date are mentioned in your title. If you want to use them to achieve your needs, you can refer to the following statement:

    ;with cte  
    as(select distinct a.TY_REF,a.TY_DESC,a.TY_POS,a.TY_STA_POS,b.PT_DATE_START,b.PT_id,  
       from [ty_dwh].[dbo].[ty_supps] as a  
       inner join [ty_dwh].[dbo].[pt_supp] as b  
       on a.RS_REF = b.RS_REF)  
      
    select * from cte  
    where b.PT_DATE_START=(select max(PT_DATE_START) from [ty_dwh].[dbo].[pt_supp])  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][1] to enable e-mail notifications if you want to receive the related email notification for this thread.