Subject Re: [firebird-support] Doubt (Problem) In Writing SQL
Author Vishal Tiwari
Post date 2017-07-05T18:15:29Z
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');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '777', 'Willy', 'Branch1', '25.01.2010', '300.000000', '250.000000', '00000086:00000003');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '555', 'Willy', 'Branch1', '25.01.2010', '900.000000', '800.000000', '00000086:00000004');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '111', 'Tata Mcgraw Hill', 'Branch1', '25.01.2010', '750.000000', '700.000000', '00000086:00000005');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '666', 'Tata Mcgraw Hill', 'Branch1', '25.01.2010', '250.000000', '200.000000', '00000086:00000006');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '888', 'Tata Mcgraw Hill', 'Branch1', '25.01.2010', '600.000000', '500.000000', '00000086:00000007');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '999', 'Tata Mcgraw Hill', 'Branch1', '25.01.2010', '400.000000', '350.000000', '00000086:00000008');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '222', 'Willy', 'Branch2', '25.01.2010', '550.000000', '500.000000', '00000086:00000009');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '333', 'Willy', 'Branch2', '25.01.2010', '450.000000', '400.000000', '00000086:0000000a');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '777', 'Willy', 'Branch2', '25.01.2010', '300.000000', '250.000000', '00000086:0000000b');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '555', 'Willy', 'Branch2', '25.01.2010', '900.000000', '800.000000', '00000086:0000000c');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('9', '111', 'Tata Mcgraw Hill', 'Branch2', '25.01.2010', '750.000000', '700.000000', '00000086:0000000d');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('9', '666', 'Tata Mcgraw Hill', 'Branch2', '25.01.2010', '250.000000', '200.000000', '00000086:0000000e');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '888', 'Tata Mcgraw Hill', 'Branch2', '25.01.2010', '600.000000', '500.000000', '00000086:0000000f');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '999', 'Tata Mcgraw Hill', 'Branch2', '25.01.2010', '400.000000', '350.000000', '00000086:00000010');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '222', 'Willy', 'Branch1', '30.01.2010', '650.000000', '600.000000', '00000086:00000011');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '333', 'Willy', 'Branch1', '30.01.2010', '750.000000', '700.000000', '00000086:00000012');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '777', 'Willy', 'Branch1', '30.01.2010', '400.000000', '350.000000', '00000086:00000013');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '555', 'Willy', 'Branch1', '30.01.2010', '800.000000', '700.000000', '00000086:00000014');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '111', 'Tata Mcgraw Hill', 'Branch1', '30.01.2010', '750.000000', '600.000000', '00000086:00000015');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '666', 'Tata Mcgraw Hill', 'Branch1', '30.01.2010', '250.000000', '100.000000', '00000086:00000016');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '888', 'Tata Mcgraw Hill', 'Branch1', '30.01.2010', '600.000000', '400.000000', '00000086:00000017');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('9', '999', 'Tata Mcgraw Hill', 'Branch1', '30.01.2010', '400.000000', '250.000000', '00000086:00000018');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '222', 'Willy', 'Branch2', '30.01.2010', '650.000000', '600.000000', '00000086:00000019');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('6', '333', 'Willy', 'Branch2', '30.01.2010', '750.000000', '700.000000', '00000086:0000001a');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '777', 'Willy', 'Branch2', '30.01.2010', '400.000000', '350.000000', '00000086:0000001b');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('7', '555', 'Willy', 'Branch2', '30.01.2010', '800.000000', '700.000000', '00000086:0000001c');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '111', 'Tata Mcgraw Hill', 'Branch2', '30.01.2010', '750.000000', '600.000000', '00000086:0000001d');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '666', 'Tata Mcgraw Hill', 'Branch2', '30.01.2010', '250.000000', '100.000000', '00000086:0000001e');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('8', '888', 'Tata Mcgraw Hill', 'Branch2', '30.01.2010', '600.000000', '400.000000', '00000086:0000001f');
INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('9', '999', 'Tata Mcgraw Hill', 'Branch2', '30.01.2010', '400.000000', '250.000000', '00000086:00000020');


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 


With Best Regards.

Vishal


On Sunday, 29 May 2011 8:49 AM, Vishal Tiwari <vishualsoft@...> wrote:


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