Strange select.

I have an order table and a line item table that has multiple entries Header and line items, but I only want to retrieve the first occurrence of the order header;

select o.bt_id,o.ORDER_NUMBER,o.total_charges,o.source_code
from Orders o
inner join Order_Lines ol on o.ORDER_NUMBER = ol.order_number and ol.PRODUCT_CODE = 'FIELDVALUE'
order by o.bt_id

returns multiple lines for each user, I only want the first occurence
Jeff_KingstonAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
SELECT *
FROM
(
select o.bt_id,o.ORDER_NUMBER,o.total_charges,o.source_code
	,rn = ROW_NUMBER() OVER (PARTITION BY o.bt_id ORDER BY o.ORDER_NUMBER)
from Orders o
inner join Order_Lines ol on o.ORDER_NUMBER = ol.order_number and ol.PRODUCT_CODE = 'FIELDVALUE'
) T
WHERE rn=1
order by bt_id

Open in new window

0
 
Patrick MatthewsCommented:
"First occurence" meaning what, exactly?

It would be helpful to see some sample data showing what's in the source tables, and what result you're expecting.
0
 
Jeff_KingstonAuthor Commented:
101893      12761333.00      66.25      WEB
101893      13242872.00      -2.02      
101893      13243075.00      -2.02      
101893      13404457.00      66.25      WEB
101893      13571055.00      66.25      WEB

and I only want the first entry.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sarabhaiCommented:
can u try out following..

SELECT o.bt_id,o.ORDER_NUMBER,o.total_charges,o.source_code
FROM Orders o
INNER JOIN (SELECT TOP 1 * FROM Order_Lines) ol ON o.ORDER_NUMBER = ol.order_number AND ol.PRODUCT_CODE = 'FIELDVALUE'
ORDER BY o.bt_id
0
 
Jeff_KingstonAuthor Commented:
That doesn't return any rows at all?
0
 
Jeff_KingstonAuthor Commented:
All I want is the first occurence for each customer.


Cust            Order                 charges
101893      12761333.00      66.25      WEB
101893      13242872.00      -2.02      
101893      13243075.00      -2.02      
101893      13404457.00      66.25      WEB
101893      13571055.00      66.25      WEB
222222      15761333.00      78.45      WEB
222222      16404457.00      78.45      WEB
222222      16571055.00      78.45      WEB
345612      14761133.00      56.42      WEB
345612      18904457.00      56.42      WEB
345612      19071055.00      56.42      WEB
0
 
Jeff_KingstonAuthor Commented:
That is a syntax I have never seen and it works like a charm.  You have saved my brain fom exploding.

Excellent job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.