Reputation: 21
I need to link and additional table to filter by the plant in doing so create multiple records for each entry. I am looking to just get the first entry for one.
SELECT
OrderHead.CreditOverride,
OrderHead.OpenOrder, Customer.CreditHold,
OrderHead.OrderNum, Customer.Name,
OrderHead.EntryPerson, OrderHead.OrderDate,
Customer.TermsCode, OrderHead.ShipToCustNum, OrderRel.Plant
FROM
Customer
INNER JOIN
OrderHead ON Customer.Company = OrderHead.Company
AND Customer.CustNum = OrderHead.BTCustNum
INNER JOIN
OrderRel ON OrderHead.OrderNum = OrderRel.OrderNum
WHERE
(OrderHead.CreditOverride = 0)
AND (OrderHead.OpenOrder = 1)
AND (Customer.CreditHold = 1)
AND (OrderRel.Plant = 'mfgsys')
Trying to grab the first unique record from orderhead.
Upvotes: 0
Views: 50
Reputation: 1271231
Use window functions. You can do this entirely in the FROM
clause:
FROM Customer c INNER JOIN
(SELECT oh.*,
ROW_NUMBER() OVER (PARTITION BY Company, BTCustNum ORDER BY OrderDate ASC) as seqnum
FROM OrderHead oh
) oh
ON c.Company = oh.Company AND
c.CustNum = oh.BTCustNum AND
oh.seqnum = 1 INNER JOIN
OrderRel orr
ON oh.OrderNum = orr.OrderNum
Note that I replaced the table names with simpler table aliases, which you should repeat in the rest of the query.
Upvotes: 1