Display only sub order number that have MOrderNo

user_14321253 0
Viewed 70

I have 2 columns which are MOrderNum and SubOrderNum. MOrderNum is for Master Order No that created after approving Sub Order No. How to only display the MSubNum that have multiple SubOrderNum?

Example

MOrderNum SubOrderNum
A123 A123-01
A123 A123-02
M111 B001
M222 C001

the data that should be displayed is

MOrderNum SubOrderNum
A123 A123-01
A123 A123-02
stored-procedures sql-server sql
3 Answers
 --it will check if MOrderNum exists in the counting list
SELECT MOrderNum, SubOrderNum
from tbl
WHERE MOrderNum in(
      --It will take all the MOrderNum with their count more than one
      SELECT MOrderNum FROM tbl
      GROUP BY MOrderNum
      HAVING COUNT(MOrderNum)>1
  )
 Order by MOrderNum

Fiddle Demo

user_101174330

You can do this more efficiently with a window function

SELECT MOrderNum, SubOrderNum
FROM (
    SELECT *,
      cnt = COUNT(*) OVER (PARTITION BY MOrderNum)
    FROM tbl
) tbl
WHERE cnt > 1;
user_148689970

this should work as well:

SELECT a.* 
FROM (
    SELECT MOrderNum
    FROM [your table]    
    GROUP BY MOrderNum
    HAVING COUNT(DISTINCT SubOrderNum) > 1  
    ) AS b 
INNER JOIN [your table] AS a              
ON a.MOrderNum = b.MOrderNum
ORDER BY a. MOrderNum
user_160190320
Related