How do I get the latest record only based on the invcreated

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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

Posted 22-May-19 19:32pm

Sasiit

Updated 22-May-19 21:30pm

v3

Add a Solution

2 solutions

Solution 1

Try:

SQL

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?

Posted 22-May-19 20:08pm

OriginalGriff

Updated 22-May-19 20:10pm

v2

Comments

Sasiit 23-May-19 3:14am

There are more than 1lak records. I am selecting only particular job only. so top 1 is unusable one.

OriginalGriff 23-May-19 3:20am

You are going to have to explain in much better detail what exactly you want then: your question specifically says "the latest record only" which means "only one record", and your example shows only one record in the output. Remember, we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.

Sasiit 23-May-19 3:18am

I got the solution thanks for the response.

Sasiit 23-May-19 3:27am

Sorry for that OriginalGriff.It's my mistake anyway thanks.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sasiit · Accepted Answer · 2019-05-22T21:19:00

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