CODEWITHSUNDEEP
sql-server
Richard
Richard

Reputation: 137

T sql find same values and order by

I have table Customer with these columns:

sales_ID varchar(50)
product_ID int
ProcessNumber int
CreateDate datetime

and this sample data:

sales_Id       product_id    ProcessNumber
---------------------------------------------  
00000020041       1733           15
00000020041       1733           15    
00000020041       1733           15

Question:

If I use this query

select * 
from 
    (select 
        *,
        row_number() over(PARTITION BY sales_Id, product_ID, ProcessNumber
                          ORDER BY xdate) rown
     from Customer) tbl 
where 
     rown > 1 
order by 
     sales_ID

Results:

1. 00000020041      1733       15

But I want query to display this result

 1. 00000020041      1733       15
 2. 00000020041      1733       15
 3. 00000020041      1733       15

What to change in my query according to select all duplicates values (2 rows , 3 rows , 4 rows...)?

Upvotes: 1

Views: 41

Answers (2)

GarethD
GarethD

Reputation: 69789

Change ROW_NUMBER() OVER() to COUNT(*) OVER(), e.g.

SELECT  *
FROM    (   SELECT  *,
                    CountOfRowsInGroup = COUNT(*) OVER (
                        PARTITION BY sales_Id,product_ID,ProcessNumber)
            FROM    Customer
        ) AS tbl
WHERE   CountOfRowsInGroup > 1
ORDER BY Sales_ID;

This will return all duplicates

Upvotes: 3

Simon UK
Simon UK

Reputation: 194

Look up RANK and DENSE_RANK. I better reword that as it's misleading: If you had 3 duplicates for RANK records might be numbered 1, 1, 1, 4, 5, 6 etc. If you had 3 duplicates for DENSE_RANK you'd get 1, 1, 1, 2, 3, 4 etc.

Upvotes: 0

Related Questions