如何仅根据创建的内容获取最新记录 [英] How do I get the latest record only based on the invcreated

问题描述

如何根据创建的内容获取最新记录。有超过1条记录和相同的jobno有超过100条记录。在一个例子中，我只参加了一项工作

现在查询返回

 JobOrderID JobNo INVInvoiceid INVInvoiceNo INVStatusID INVInvoiceModeID INVCreateddate 
 -------------------- ------------------ ------- -------------------- -------------------- --- -------- ---------------- ----------------------- 
 2802 13011900263 43877 INF220519025921 7 2 2019-05-22 15：16：00.000 
 2802 13011900263 43881 NULL 1 2 2019-05-22 16：29：00.000 

但我需要

 JobOrderID JobNo INVInvoiceid INVInvoiceNo INVStatusID INVInvoiceModeID INVCreateddate 
 ---- ---------------- ------------------------- --------- ----------- -------------------- ----------- ----- ----------- ----------------------- 
 2802 13011900263 43881 NULL 1 2 2019-05-22 16 ：29：00.000 

我尝试过：

选择
 J.JobOrderID，J.JobNo，
 INV.InvoiceID as INVInvoiceid，INV.InvoiceNo as INVInvoiceNo，INV.StatusID as INVStatusID，INV.InvoiceModeID as INVInvoiceModeID，
 INV.Createddate as INVCreateddate 
 from ffs_tbl_joborder J 
 inner join FFS_Tbl_AirJob AJ on AJ.AirJobID = J.JobOrderID and AJ.JobNo = J.JobNo 
 left join FFS_TBl_Invoice INV在INV.JobOrderID = J.JobOrderID和INV.JobNo = J.JobNo和INV.JobType = J.JobType 
其中J.jobno ='13011900263'和INV.JobType ='AirJob'
 group by J.JobOrderID，J.JobType，J.JobNo，J.Status，AJ.Status，INV.InvoiceID，INV.InvoiceNo，INV.StatusID，INV.InvoiceModeID，INV.Createddate 

解决方案

尝试：

  SELECT   TOP   1  
 ... 
  ORDER   BY  INV.Createddate  DESC  

虽然我不得不说GROUP BY子句看起来非常可疑 - 如果组中包含每个输出列，你会意识到它不会有用吗？

选择
 J.JobOrderID，J.JobType，J.JobNo，J.Status作为JoborderStatus，AJ.Status作为AirJobStatus，
 isnull（INV.InvoiceID，0 ）作为INVInvoiceid，isnull（INV.InvoiceNo，0）as INVInvoiceNo，isnull（INV.StatusID，0）as INVStatusID，isnull（INV.InvoiceModeID，0）as INVInvoiceModeID 
 from ffs_tbl_joborder J 
 inner join FFS_Tbl_AirJob AJ在AJ.AirJobID = J.JobOrderID和AJ.JobNo = J.JobNo 
左连接（
选择JobOrderID，JobNo，JobType，InvoiceID，isnull（InvoiceNo，0）为InvoiceNo，StatusID，InvoiceModeID， createddate，Minchk来自（
选择INV .JobOrderID，INV.JobNo，INV.JobType，INV.InvoiceID，INV.InvoiceNo，INV.StatusID，INV.InvoiceModeID，INV.CreatedDate，row_number（）over（JobOrderID分区，CreatedDate desc的JobType顺序）为Minchk $ b来自FFS_TBl_Invoice INV的$ b其中JobType in（'AirJob'，'AirHouseJob'）
）as t1 
其中Minchk = 1 
）
 INV on INV.JobOrderID = J.JobOrderID和INV.JobNo = J.JobNo和INV.JobType = J.JobType 
其中
 INV.JobType in（'AirJob'，'AirHouseJob'）和J.JobOrderID = 2802 
 group by J.JobOrderID，J.JobType，J.JobNo，J.Status，AJ.Status，INV.InvoiceID，INV.InvoiceNo，INV.StatusID，INV.InvoiceModeID 

How do I get the latest record only based on the invcreated. there are more than 1lak records and same jobno having more than 100 records. In an example, I am taken only one jobno

Now the Query Returns

JobOrderID           JobNo                     INVInvoiceid         INVInvoiceNo         INVStatusID INVInvoiceModeID INVCreateddate
-------------------- ------------------------- -------------------- -------------------- ----------- ---------------- -----------------------
2802                 13011900263               43877                INF220519025921      7           2                2019-05-22 15:16:00.000
2802                 13011900263               43881                NULL                 1           2                2019-05-22 16:29:00.000

But I Need

JobOrderID           JobNo                     INVInvoiceid         INVInvoiceNo         INVStatusID INVInvoiceModeID INVCreateddate
-------------------- ------------------------- -------------------- -------------------- ----------- ---------------- -----------------------
2802                 13011900263               43881                NULL                 1           2                2019-05-22 16:29:00.000

What I have tried:

select 
J.JobOrderID,J.JobNo,
INV.InvoiceID as INVInvoiceid,INV.InvoiceNo as INVInvoiceNo,INV.StatusID as INVStatusID,INV.InvoiceModeID as INVInvoiceModeID,
INV.Createddate as INVCreateddate
from  ffs_tbl_joborder J  
inner join FFS_Tbl_AirJob AJ on AJ.AirJobID=J.JobOrderID and AJ.JobNo=J.JobNo 
left join FFS_TBl_Invoice INV on INV.JobOrderID=J.JobOrderID and INV.JobNo=J.JobNo  and INV.JobType=J.JobType
where J.jobno='13011900263' and INV.JobType='AirJob' 
group by J.JobOrderID,J.JobType,J.JobNo,J.Status,AJ.Status,INV.InvoiceID,INV.InvoiceNo,INV.StatusID,INV.InvoiceModeID,INV.Createddate 

解决方案

Try:

SELECT TOP 1
...
ORDER BY INV.Createddate DESC

Though I have to say the GROUP BY clause looks very suspect - you do realise it won't do anything useful if you have every output column included in the group?

select 
J.JobOrderID,J.JobType,J.JobNo,J.Status as JoborderStatus,AJ.Status as AirJobStatus,
isnull(INV.InvoiceID,0) as INVInvoiceid,isnull(INV.InvoiceNo,0) as INVInvoiceNo,isnull(INV.StatusID,0) as INVStatusID,isnull(INV.InvoiceModeID,0) as INVInvoiceModeID
from  ffs_tbl_joborder J  
inner join FFS_Tbl_AirJob AJ on AJ.AirJobID=J.JobOrderID and AJ.JobNo=J.JobNo 
left join (
select JobOrderID,JobNo,JobType,InvoiceID,isnull(InvoiceNo,0) as InvoiceNo ,StatusID,InvoiceModeID,createddate,Minchk from (
 select INV.JobOrderID,INV.JobNo,INV.JobType,INV.InvoiceID,INV.InvoiceNo,INV.StatusID,INV.InvoiceModeID,INV.CreatedDate, row_number() over(partition by JobOrderID,JobType order by CreatedDate desc) as Minchk 
 from FFS_TBl_Invoice INV where JobType in ('AirJob','AirHouseJob')
 ) as t1
 where Minchk=1 
 )
INV on INV.JobOrderID=J.JobOrderID and INV.JobNo=J.JobNo  and INV.JobType=J.JobType
where 
INV.JobType in ('AirJob','AirHouseJob') and J.JobOrderID=2802
group by J.JobOrderID,J.JobType,J.JobNo,J.Status,AJ.Status,INV.InvoiceID,INV.InvoiceNo,INV.StatusID,INV.InvoiceModeID

这篇关于如何仅根据创建的内容获取最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！