I am providing hypothetical table and query to explain my problem. Pardon me for any sntax error.
Dept table:
ID Dno Dname BDate seq
1 1 A 5-Aug 0
2 1 B 3-Aug 0
3 1 B 7-Aug 1
4 1 C 2-Aug 0
Below query returns 1st, and 3rd, record from above table:
select * from Dept where BDate > (select mydate from other_table)
-- mydate is 4-Aug
Then I did below changes in the query to return 2nd record as well. Because for Dname 'B', we have one record with Bdate > 4-Aug.
select * from Dept D where
(SELECT MAX(BDATE)
FROM Dept D1
WHERE D1.Dno = D.Dno
AND D1.Dname = D.Dname
) > (select mydate from other_table)
Above query works but it hit the performance. How could I optimize it.
I think of Union or moving max query to select part. But, not able to find a way.
Best Answer
Assuming I understand your logic correctly (that you want all rows for a given dno and dname if the max date is greater than a specified date) and that the query to retrieve the "mydate" returns a single row, I would do something like: