Subject Re: [firebird-support] Doubt (Problem) In Writing SQL
Author setysvar
Den 05.07.2017 20:15, skrev Vishal Tiwari vishualsoft@... [firebird-support]:
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