SQL: Adding another MAX field to existing MAX Date Query-CodePudding

I have an existing query that is picking up the max date of the exdt field per below for every record for that particular item. I'm trying to add an additional MAX calculation to the sqn field, essentially picking up records with the highest exdt and sqn field together. I tried adding an additional "AND (a.sqn = (SELECT MAX(sqn) to the WHERE and it seems to not like multiple selects.

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn

FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc

WHERE (a.exdt = (SELECT MAX(exdt) AS exdt FROM  dbo.ct AS a

Any help would be appreciated!

Thanks.

CodePudding user response：

The script to get records having max of both fields is the following

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn
FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc
WHERE 
a.exdt = (SELECT MAX(exdt) AS exdt FROM  dbo.ct )
and 
a.sqn= (SELECT MAX(sqn) AS exdt FROM  dbo.ct)

You can also use CTEas follows

with 
cte_principal as
(SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn
 FROM     dbo.ct AS a INNER JOIN dbo.dt ON a.cpc = dt.cpc),
cte_max_exdt as (SELECT MAX(exdt) AS exdt FROM  dbo.ct),
cte_max_sqn as (SELECT MAX(sqn) AS exdt FROM  dbo.ct)

select p.* from cte_principal p  
inner join cte_max_exdt  on p.exdt =cte_max_exdt.exdt
inner join cte_max_sqn  pn p.sqn=cte_max_sqn.sqn

CodePudding user response：

Perhaps you need subquery which is going to find max date for each item. That is ct_max_date in my case After that you join that subquery by max_date and item_Id

SELECT a.item, a.cpc, dt.dsc, a.exdt, a.sqn

FROM     dbo.ct AS a 
INNER JOIN dbo.dt ON a.cpc = dt.cpc
inner join (
select max(exdt) as max_date, ct.cpc
from dbo.ct
group by ct.dt
) as ct_max_date on a.cpc=ct_max_date.cpc and a.exdt=ct_max_date.max_date