dubasir
New User
Joined: 05 Jun 2006 Posts: 19
|
|
|
|
I have a requirement where I have to select maximum effective date <= current _date for a given group and then delete the rows for the group with effective date < maximum effective date obtained in the previous query.
As of now, I am able to achieve this task by using the below 2 queries.
Query 1:
SELECT DISTINCT A,
B,
C,
D,
E,
F,
G,
MAX(DATE_EFF)
FROM Table1
WHERE DATE_EFF <= CURRENT_DATE
GROUP BY A,B,C,D,E,F,G
Query2:
DELETE FROM Table1
WHERE A = &A
AND B = &B
AND C = &C
AND D = &D
AND E = &E
AND F = &F
AND G = &G
AND DATE_EFF < &MAX(DATE_EFF) /* This is obtained from the previous query */
Please let us know how they can be merged into a single query. |
|