Solved

Strange select.

Posted on 2013-06-06
7
207 Views
Last Modified: 2013-06-06
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
0
Comment
Question by:Jeff_Kingston
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
"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
 

Author Comment

by:Jeff_Kingston
Comment Utility
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
 
LVL 9

Expert Comment

by:sarabhai
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Jeff_Kingston
Comment Utility
That doesn't return any rows at all?
0
 

Author Comment

by:Jeff_Kingston
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Jeff_Kingston
Comment Utility
That is a syntax I have never seen and it works like a charm.  You have saved my brain fom exploding.

Excellent job!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now