CODEWITHSUNDEEP
sqloracle-database
bontade
bontade

Reputation: 3224

Get references without subquery

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

sgeddes
sgeddes

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

Related Questions