Reputation: 3224
I have the following data in table:
select p.number, pr.refNumber from part p, partref pr where pr.refNumber = p.id;
number refNumber
---------------------------------------- ----------------------
1 1
2 2
2 3
2 4
I need to remove old partrefs, only one partref should exist for part. At first I need to get all partrefs that should be deleted. Is that possible to do it without subquery (subselect)? How to do it?
UPDATE.
"OLD" partrefs are all partref records that aren't created at the latest. So for example:
refNumber creationDate
---------------------------------------- ----------------------
1 01-01-13
2 01-02-13
3 02-02-13
4 03-02-13
For part with id = 2, exists multiple partrefs like 2,3,4. Only 4th partref shouldn't be deleted since it's created at the latest. Partrefs with refNumber 2 and 3 should be removed.
Upvotes: 1
Views: 51
Reputation: 1271241
I think this is a very good place for analytic functions. Getting the ref numbers you want to keep is pretty easy. However, it does still require a subquery:
select *
from (select p.number, pr.refNumber, pr.CreationDate,
MAX(ref.CreationDate) over (p.Number) as MaxCreationDate
from part p join
partref pr
on pr.refNumber = p.id
) ppr
where CreationDate <> MaxCreationDate
By the way, why the aversion to subqueries? They are tremendously useful and a vital part of SQL.
Also, you should use proper ANSI join syntax.
Upvotes: 0
Reputation: 62861
Given your edits, this should work:
SELECT DISTINCT p.num,
MAX(pr.refNumber) OVER (PARTITION BY p.Num ORDER BY CreationDate DESC) as refNumberToKeep
FROM part p
INNER JOIN partref pr ON pr.refNumber = p.id
And here is the SQL Fiddle.
Good luck.
Upvotes: 2