Hi SET,
 
Extreamely sorry for too late reply, coz was busy in project work.
 
But I am delighted as usual by reading mail....
 
I have not gone through you mail, soafter this i would go through yur mail and surly get back to you with some more doubts on this example.
 
 
Thanking You And With Best Regards.
 
 
To My Master-Trouble Shooter.
 
 
Vishal 

--- On Wed, 18/5/11, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: [firebird-support] Doubt (Problem) In Writing SQL
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Date: Wednesday, 18 May, 2011, 11:08 AM


 



> Given
>
> Book Book Group Main Sub Publish Original Selling
> NO ID Code Branch Branch Date Price Price
>
> 3 23 'AA02' 'KAR' 'KAR' '2011-03-01' 13.25 13.25
> 5 33 'AWAP' 'KAR' 'KAR' '2011-05-18' 13.45 13.45
> 5 55 'AWAP' 'KAR' 'KAR' '2011-05-18' 12.65 10.65
> 4 57 'AA02' 'KAR' 'KAR' '2011-05-18' 11.12 11.12
> 4 59 'AA02' 'KAR' 'KAR' '2011-05-20' 13.75 13.75
> 5 65 'AWAP' 'KAR' 'KAR' '2011-05-23' 13.25 13.25
> 6 77 '14128' 'KAR' 'KAR' '2011-05-23' 10.65 13.65
> 5 333 'AWAP' 'KEL' 'KEL' '2011-05-18' 13.45 13.45
> 5 355 'AWAP' 'KEL' 'KEL' '2011-05-18' 9.65 5.65
> 4 357 'AA02' 'KEL' 'KEL' '2011-05-18' 11.125 11.12
>
> Here I would like to get the result as, for Each Date, for Each Main Branch and for Each Sub Branch,
> look for Maximum value of "Book_NO" column, if there are some, same number of values exist for "Book_No"
> column for any respective date, then, look for Maximum value of the "Book_ID" column, for the same
> "Book_NO" value, and for Maximum Value of the "Book_ID", take the "Original_Price" and "Selling_Price"
> column values.
>
> So the entire sql should give following columns and the values:
>
> Book Book Group Main Sub Publish Original Selling
> NO ID Code Branch Branch Date Price Price
>
> 3 23 'AA02' 'KAR' 'KAR' '2011-03-01' 13.25 13.25
> 5 55 'AWAP' 'KAR' 'KAR' '2011-05-18' 12.65 10.65
> 5 355 'AWAP' 'KEL' 'KEL' '2011-05-18' 9.65 5.65
> 4 59 'AA02' 'KAR' 'KAR' '2011-05-20' 13.75 13.75
> 6 77 '14128' 'KAR' 'KAR' '2011-05-23' 10.65 13.65

Think of your question the opposite way, Vishal, and just eliminate rows that doesn't fit your requirement. Then, your question can be solved by using NOT EXISTS or the equivalent LEFT JOIN ... WHERE ... IS NULL, i.e. (if any row matches the BD2 requirements, then the BD1 row shouldn't be returned)

SELECT BD1.*
FROM BOOK_DETAILS BD1
LEFT JOIN BOOK_DETAILS BD2
ON BD1.PUBLISH_DATE = BD2.PUBLISH_DATE
AND BD1.MAIN_BRANCH = BD2. MAIN_BRANCH
AND BD1.SUB_BRANCH = BD2.SUB_BRANCH
AND (BD1.BOOK_NO < BD2.BOOK_NO
OR (BD1.BOOK_NO = BD2.BOOK_NO
AND BD1.BOOK_ID < BD2.BOOK_ID))
WHERE BD2.BOOK_ID IS NULL

HTH,
Set







[Non-text portions of this message have been removed]