Hi SET,

Just for you my Friend... Please check...

CREATE TABLE BOOK_SELLING_DETAIL

(

  BOOK_NO Integer,

  BOOK_ID Integer,

  PUBLISHER Varchar(50),

  MAIN_BRACH Varchar(10),

  PUBLISH_DATE Date,

  ORIGINAL_PRICE Float,

  SELLING_PRICE Float

);

INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '222', 'Willy', 'Branch1', '25.01.2010', '550.000000', '500.000000', '00000086:00000001');

INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '333', 'Willy', 'Branch1', '25.01.2010', '450.000000', '400.000000', '00000086:00000002');

...

For every Publisher, for its same Branch and for the same date find the Maximum Book_No value,

if there are multiple records of the same maximum Book_No found then 

find the Maximum Book_ID value for that maximum Book_No and get all the details of that Book_ID row. 

SQL:

SELECT BSD.BOOK_NO, BSD.BOOK_ID, BSD.PUBLISHER, BSD.MAIN_BRACH, BSD.PUBLISH_DATE, BSD.ORIGINAL_PRICE, BSD.SELLING_PRICE

FROM BOOK_SELLING_DETAIL BSD

Inner Join 

(

    Select BSD1.BOOK_NO, Max(BSD1.BOOK_ID) As BOOK_ID, BSD1.PUBLISHER, BSD1.MAIN_BRACH, BSD1.PUBLISH_DATE from BOOK_SELLING_DETAIL BSD1

    Inner join

    (

        Select Max(BOOK_NO) As BOOK_NO, PUBLISHER, MAIN_BRACH, PUBLISH_DATE from BOOK_SELLING_DETAIL

        Group By PUBLISHER, MAIN_BRACH, PUBLISH_DATE

    ) BSD2

    ON 

    BSD1.PUBLISHER = BSD2.PUBLISHER And BSD1.MAIN_BRACH = BSD2.MAIN_BRACH And BSD1.PUBLISH_DATE = BSD2.PUBLISH_DATE And BSD1.BOOK_NO = BSD2.BOOK_NO 

    

    Group By BSD1.PUBLISHER, BSD1.MAIN_BRACH, BSD1.PUBLISH_DATE, BSD1.BOOK_NO  

) BSD3

ON 

BSD.BOOK_NO = BSD3.BOOK_NO And BSD.BOOK_ID = BSD3.BOOK_ID And BSD.PUBLISHER = BSD3.PUBLISHER And BSD.MAIN_BRACH = BSD3.MAIN_BRACH And BSD.PUBLISH_DATE = BSD3.PUBLISH_DATE 

Order By BSD.PUBLISHER, BSD.MAIN_BRACH, BSD.PUBLISH_DATE, BSD.BOOK_NO, BSD.BOOK_ID

Hi Vishal!

Thanks for the DDL and example values you provided. I didn't have the time and energy required to look at this before today, and quite frankly, I haven't looked carefully at your SQL. Though I wonder if your question can be rephrased:

For each PUBLISHER, MAIN_BRACH and PUBLISH_DATE, find the record with the highest value for BOOK_NO. In case of duplicates, choose the one with the highest BOOK_ID.

If so, try this (simpler) SQL:

SELECT BSD.BOOK_NO, BSD.BOOK_ID, BSD.PUBLISHER, BSD.MAIN_BRACH, BSD.PUBLISH_DATE, BSD.ORIGINAL_PRICE, BSD.SELLING_PRICE
FROM BOOK_SELLING_DETAIL BSD
WHERE NOT EXISTS( SELECT *
                  FROM BOOK_SELLING_DETAIL BSD2
                  WHERE BSD.PUBLISHER    = BSD2.PUBLISHER
                    AND BSD.MAIN_BRACH   = BSD2.MAIN_BRACH
                    AND BSD.PUBLISH_DATE = BSD2.PUBLISH_DATE
                    AND ( BSD.BOOK_NO    < BSD2.BOOK_NO
                       OR ( BSD.BOOK_NO  = BSD2.BOOK_NO
                        AND BSD.BOOK_ID  < BSD2.BOOK_ID ) ) )

HTH,
Set