Solved

Limit SQL Output in Excel

Posted on 2011-03-10
3
231 Views
Last Modified: 2012-05-11
I've got a Microsoft Query set up between a sql database and excel 2010 that I'm trying to filter out some information.  Attached is a copy of the report in its current state.  You'll see that the attached customer has two jobs in my system, all of which have multiple steps (Work Centers).  What I'd like to obtain is limiting the output of this script so that it only shows the first Work Center, which varies depending on the job.  Is this possible?  Attached is my SQL Code generated by Excel.  I tried using LIMIT 1 on the function at the end, but it tells me the code has errors when I apply it. "Job.Job" is the field that contains the job number, which is how I would like to limit it.  

Thank You for your help!

example.pdf
SELECT Job.Job, Job.Order_Date, Job.Customer, Job.Customer_PO, Job.Part_Number, Job.Customer_PO_LN, Job.Order_Quantity, Job.Status, Job.Sched_Start, Job.Sched_End, Job_Operation.Work_Center, Job_Operation.Status
FROM PRODUCTION.dbo.Job Job, PRODUCTION.dbo.Job_Operation Job_Operation
WHERE Job.Job = Job_Operation.Job AND ((Job.Status='Active') AND (Job_Operation.Status<>'C'))
ORDER BY Job.Customer

Open in new window

0
Comment
Question by:Kyle Witter
  • 2
3 Comments
 
LVL 7

Expert Comment

by:andymacf
ID: 35095398
Would the first work centre be the most recent one, if so, you could set your query to look for 'max' value on the relevant field and this will always pull in the first event.
0
 
LVL 1

Author Comment

by:Kyle Witter
ID: 35095440
The first work center is the location that the job is currently at in production.  This is the line that I would like to keep.  I guess I don't understand exactly how to set a max value to limit the output, though.

Do you have an example?  Thank you!
0
 
LVL 7

Accepted Solution

by:
andymacf earned 500 total points
ID: 35096818
Difficult to give you an example right away as I am away from my pc, but try this. In your SQL, try wrapping the value 'max(your value)… Select max(value) from
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

10 Experts available now in Live!

Get 1:1 Help Now