August 18, 2002 at 8:28 pm
G'day,
I'm trying to use distinct to give me a specific result set. What I want to do is pull only one instance of a customer id out of a table called invoiced if invoicing occurred in the last 6 months and the order type is service_order. The query I'm using for this is:
select distinct custid
from invoiced
where doc_type_id = 'service_order' and
created_at >= '2002-02-01 00:00:00'
order by cust_company_id
This part runs ok. Now the question is, how do I join more columns to the query? ie I also want to include in the result set a unique invoice id from the invoiced table, as well as join other tables to the query. I've tried using a subquery like this:
select invoiceid, custid
from invoiced
where
(select distinct custid
from invoiced
where doc_type_id = 'service_order' and
created_at >= '2002-02-01 00:00:00'
order by custid)
and I get the following error:
An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
Any ideas appreciated.
Matt.
August 18, 2002 at 8:44 pm
One way...not the best, is to use a top 100 percent in the query, that will allow an order by.
Andy
August 18, 2002 at 9:16 pm
In the derived table, you don't need the ORDER BY. Move the ORDER BY outside of the parentheses and you should be fine. SQL Server doesn't need custid sorted in order to return the results you want.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 18, 2002 at 9:18 pm
Hi Andy,
BOL has the following info on TOP:
SELECT [ ALL | DISTINCT ]
[ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
I've tried using top:
select invoice_id, custid
from invoiced
where
(select distinct custid
top 100 percent
from invoiced
where
doc_type_id = 'service_order' and
created_at >= '2002-02-01 00:00:00'
order by custid)
but I get errors when I run this. Is it possible that my whole subquery is incorrect? Also, do I use TOP in the subquery or in the main select statement?
August 18, 2002 at 9:28 pm
Hey Brian,
Yup, I moved the order by clause outside of the parentheses to no avail. Even if I remove the order by completely it now gives an error:
Line 8: Incorrect syntax near ')'.
I feel I may be making a fundamental mistake, just can't nail it down at the minute.
August 19, 2002 at 12:31 am
I think you should write the query like this. The remaining question is whether you want to return all the invoices from the selected customers (like this query does), or just a single, specific invoice?
select invoiceid, custid
from invoiced i1
where EXISTS
(select custid
from invoiced i2
where doc_type_id = 'service_order' and
created_at >= '2002-02-01 00:00:00' and
i1.custid = i2.custid)
order by custid
August 19, 2002 at 3:12 am
As NPeeters has done, the WHERE will need to have some expression where a true or false value can be obtained. With the original query, just moving the ORDER BY out doesn't accomplish. I apologize, I should have mentioned that. Using EXISTS or custid IN would give you something to evaluate for a true or false value.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply