sql - Get references without subquery -

sql - Get references without subquery -

i have next info in table:

select p.number, pr.refnumber part p, partref pr pr.refnumber = p.id; number refnumber ---------------------------------------- ---------------------- 1 1 2 2 2 3 2 4

i need remove old partrefs, 1 partref should exist part. @ first need partrefs should deleted. possible without subquery (subselect)? how it?

update.

"old" partrefs partref records aren't created @ latest. example:

refnumber creationdate ---------------------------------------- ---------------------- 1 01-01-13 2 01-02-13 3 02-02-13 4 03-02-13

for part id = 2, exists multiple partrefs 2,3,4. 4th partref shouldn't deleted since it's created @ latest. partrefs refnumber 2 , 3 should removed.

given edits, should work:

select distinct p.num, max(pr.refnumber) on (partition p.num order creationdate desc) refnumbertokeep part p inner bring together partref pr on pr.refnumber = p.id

and here sql fiddle.

good luck.

sql oracle