Den 05.07.2017 20:15, skrev Vishal Tiwari vishuals...@yahoo.co.in
[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
