如何从查询中获取最大日期记录 [英] how to get max date record from query

问题描述

SELECT       
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM           
dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
where T1.series='223' and T0.docnum=6821

推荐答案

SELECT TOP 1      
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM           
dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
where T1.series='223' and T0.docnum=6821
ORDER BY T0.Recontact DESC

每个文档
：

for each docnum:

SELECT ClgCode, DocNum, ReContact, CardName, DocTotal, CntctType,
Details, Notes, CntctTime, DocDate, U_NAME, DocTotal, Swati, Janet, PadMini, Abdel, Nitin FROM
(
SELECT ROW_NUMBER OVER (PARTITION BY T0.docnum ORDER BY T0.Recontact DESC) AS rowno,
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM

dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
) as x
where x.rowno = 1

这篇关于如何从查询中获取最大日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！