|Subject||Re: [firebird-support] Doubt (Problem) In Writing SQL|
Den 17.07.2017 13:16, skrev Vishal Tiwari vishualsoft@... [firebird-support]:
Thank You for the reply. Not an issue, I could understand.
Before I execute this SQL, would like to learn something pretty interesting logic in the SQL you provided.
I got the intention of using Exists predicate but I couldn't understand the logic behind the below condition, would you please help me in understanding?
AND ( BSD.BOOK_NO < BSD2.BOOK_NO
OR ( BSD.BOOK_NO = BSD2.BOOK_NO
AND BSD.BOOK_ID < BSD2.BOOK_ID ) ) )
Thanking You Master Trouble Shooter And With Best Regards.
Hi again, Vishal.
This is to satisfy: "find the record with the highest value for BOOK_NO. In case of duplicates, choose the one with the highest BOOK_ID"
find the record with the highest value for BOOK_NO,
i.e. There should not be anyone with a higher BOOK_NO,
i.e. (not exist) BSD.BOOK_NO < BSD2.BOOK_NO
In case of duplicates, choose the one with the highest BOOK_ID,
i.e. if the BOOK_NO is identical, there should not be anyone with a higher BOOK_ID
i.e. (not exist) BSD.BOOK_NO = BSD2.BOOK_NO AND BSD.BOOK_ID < BSD2.BOOK_ID
I could have used two separate NOT EXISTS, but
NOT EXISTS(A) AND NOT EXISTS(B) = NOT EXISTS(A OR B)
and hence I shortened it.