How to find max of date using select statement?


I’m trying to get a Journal id using maximum of posted date and time, may i know the coding to do that

select maxof(Posteddatetime) from pjt where pjt.ProdId == “IM039174” && pjt.JournalType == ProdJournalType::RouteCard && pjt.Posted==noyes::Yes;

I tried this but it not showing the record

Use order by

Check if the query returns anything without maxof(PostedDateTime). If not, your system doesn’t contain any record fulfilling your conditions.

Yes when giving without maxof the query is showing correctly but when i give maxof record is returning empty

I think you have wrong expectations. Your code will return a value only in PostedDateTime field, all other fields will be empty. This is the correct behavior, but maybe you want something else and therefore you need different code.

select firstonly JournalId from pjt
order by Posteddatetime desc

where pjt.ProdId == “IM039174” && pjt.JournalType == ProdJournalType::RouteCard && pjt.Posted==noyes::Yes;

Yes i need the last posted record for prod route, how could i fetch that?

Order records by PostedDateTime in descending order and select the first one. That’s what Kranthi meant by his reply.

For example:

select firstOnly pjt
    order by PostedDateTime desc
    where ...;

Update: Oh, I didn’t notice that Kranthi’s already answered that in another branch of this discussion.

I need to find like this, kranthi.

Based on route I need to find the journal id which was posted at last

If last operation is not posted need to check the before operations

OK. What issue do you have now?

What does this mean?

I’m not getting the last posted operation, kranthi

like above shown image

120 oprnum isnot posted but 110 is posted i need to get that…

may i have a sample code?

What data do you see in ProdJournalRoute table for that particular journal id? Have you tried finding the last operation from ProdJournalRoute table?