CODEWITHSUNDEEP
sqloracle-database
ashcanschool
ashcanschool

Reputation: 327

Joining Tables in Oracle 8i to Limit Results

My background in SQL is limited, but my Googling isn't, I feel that I might just be missing the vocabulary to ask this question properly so hopefully beyond an answer to my question I can get the vocabulary I need to research this issue further.

I have a parts table - PARTS I have a Purchase Order table - PO and I have a PO Line Item table - PO_LINEITEM

The question I'm attempting to answer is given a particular part I want to get the latest purchase order and then look at the price we paid.

The PO table holds the date (PO_DATE) of when the Purchase Order was filled and the PO_LINEITEM table holds the information regarding the particular line item such as part primary key (PART_PRIMARYKEY) and price (PART_PRICE). The PARTS table isn't as important as the rest except that I need to return a PART primary key so that I can base the resulting view off the PARTS table

I've been through sub-queries and scalable sub-queries and the like, but I can't seem to find the right combination.

I started from a base of:

SELECT a.PO_DATE, b.PART_PRIMARYKEY, b.PART_PRICE
FROM PO a, PO_LINEITEM b
WHERE a.PO_PRIMARYKEY = b.PO_PRIMARYKEY

As you would expect this returns a list of every instance of an object in a PO with it's price and the date the Purchase Order was filled. The closest I have come to crack this is by using the MAX function on the date such as:

SELECT MAX(a.PO_DATE) AS DATE, b.PART_PRIMARYKEY, b.PART_PRICE
FROM PO a, PO_LINEITEM b
WHERE a.PO_PRIMARYKEY = b.PO_PRIMARYKEY
GROUP BY b.PART_PRIMARYKEY, b.PART_PRICE

This returns a Max date for each price a we paid for a particular part, so:

PART 1234, £12.95, 12/08/2012
PART 1234, £13.00, 14/08/2012
PART 1234. £11.15, 17/08/2012
PART 2345, £5.25, 12/08/2012
PART 2345, £5.65, 13/08/2012
etc.

What I need is:

PART 1234, £11.15, 17/08/2012
PART 2345. £5.65, 13/08/2012

If I could just group by the PART_PRIMARYKEY that would be excellent, but I get an ORA-00979 not a GROUP BY expression when I try.

Like I said I feel that my lack of vocabulary around this issue is impeding me finding an answer, so if anyone could point me in the right direction I'd be grateful

So hopefully I'm not asking a question that is asked every other day, but haven't found because I didn't use the magical combination of words to find.

Thank you for any help you can offer.

Upvotes: 0

Views: 122

Answers (1)

John Doyle
John Doyle

Reputation: 7793

Look up Analytic Functions. They were introduced in 8i though I'm not sure how advanced they were at the time compared to how very good they can be in 11. A few links that I've used to understand them:

http://www.oracle-base.com/articles/misc/analytic-functions.php

http://www.orafaq.com/node/55

Though, a sub-query such as this might suffice (I may have your column naming mixed up):

select A.PART_PRIMARYKEY, B.PART_PRICE, A.PO_DATE
  from PO A, PO_LINEITEM B
 where A.PO_PRIMARYKEY = B.PO_PRIMARYKEY
       and (A.PART_PRIMARYKEY, A.PO_DATE) in
             (  select A.PART_PRIMARYKEY, max(A.PO_DATE)
                  from PO A, PO_LINEITEM B
                 where A.PO_PRIMARYKEY = B.PO_PRIMARYKEY
              group by A.PART_PRIMARYKEY);

Upvotes: 1

Related Questions