CODEWITHSUNDEEP
sqlssrs-2008-r2
Marquee Phillip
Marquee Phillip

Reputation: 21

First record in SQL statement

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions