Subject Re: [firebird-support] Doubt (Problem) In Writing SQL
Author setysvar
Den 17.07.2017 13:16, skrev Vishal Tiwari vishualsoft@... [firebird-support]:
Hi SET,

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.

Vishal

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.

HTH,
Set