Solved

Strange select.

Posted on 2013-06-06
7
210 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
ID: 39225472
"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
ID: 39225486
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
ID: 39225518
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Jeff_Kingston
ID: 39225540
That doesn't return any rows at all?
0
 

Author Comment

by:Jeff_Kingston
ID: 39225608
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
ID: 39225829
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
ID: 39225971
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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