Finding all delivery orders with multiple purchase orders

Question

I have a SQL query to find all delivery orders with multiple purchase orders (one-to-many). Is there a better or shorter way to write this?

SELECT * FROM GR_data
WHERE doNo IN
(
 SELECT dt.doNo FROM
 (
 SELECT doNo, po_number FROM GR_data 
 WHERE DATALENGTH(Mat_No)<=0
 GROUP BY doNo, po_number
 )AS dt
 GROUP BY dt.doNo
 HAVING COUNT(dt.doNo)>1
)
ORDER BY doNo, po_number

• 200_success: Please include some contextual information about what the schema is, what the uniqueness constraints are (if any), and what you aim to achieve with this query. (The title of this question should also be modified to reflect what the query is for — see How to Ask.)

Answer 1

The subquery calculates a distinct count using two step, of course this can be simplified to:

SELECT * 
FROM GR_data
WHERE doNo IN
 (
 SELECT doNo
 FROM GR_data 
 WHERE DATALENGTH(Mat_No)<=0
 GROUP BY doNo
 HAVING COUNT(DISTINCT po_number)>1
 )
ORDER BY doNo, po_number

Depending on your actual data it might be more efficient to apply a Windowed Aggregate Function:

COUNT(DISTINCT po_number) OVER (PARTITION BY doNo)

Unfortunately using DISTINCT in Analytical Functions is not supported by SQL Server.

But assuming that there are no NULLs in po_number COUNT DISTINCT OVER can be emulated using different ways, probably most efficient by applying two DENSE_RANKS:

SELECT * -- if you don't want to show "dense_cnt" you must list all other columns besides
FROM
 (
 SELECT *,
 DENSE_RANK() OVER (PARTITION BY doNo ORDER BY po_number) 
 +DENSE_RANK() OVER (PARTITION BY doNo ORDER BY po_number DESC) - 1 AS dense_count
 FROM GR_data 
 WHERE DATALENGTH(Mat_No)<=0
 ) AS dt
WHERE dense_count > 1
ORDER BY doNo, po_number

Check actual resource usage if those rewrites are more efficient.

• Nilzone-: what do you mean by Unfortunately DISTINCT is not supported by SQL Server?
• dnoeth: @Nilzone: This refers to the previous sentence, it's not supported in COUNT OVER.