Finding all delivery orders with multiple purchase orders 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)&lt;=0
        GROUP BY doNo, po_number
    )AS dt
    GROUP BY dt.doNo
    HAVING COUNT(dt.doNo)&gt;1
)
ORDER BY doNo, po_number
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)&lt;=0
   GROUP BY doNo
   HAVING COUNT(DISTINCT po_number)&gt;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)&lt;=0
 ) AS dt
WHERE dense_count &gt; 1
ORDER BY doNo, po_number


Check actual resource usage if those rewrites are more efficient.