Selecting only rows with multiple entries in SQL Server

Question

I have a table #DEL_FAC where I need to select rows which have multiple entries. I used a partition function but I not able to figure out to weed out the rows which has only one single entry based on Rec_ID.

Below example will explain little better. So, from the output I would prefer to select REC_ID values which has multiple entries. In this example Rec_ID = 1052, 12811 and 1321 are the desired results.

I don't want to explicitly mention these Rec_ID in the WHERE clause since the table has hundred's of rows. This was just an example. Any help is highly appreciated. Thanks

SELECT 
    ID, NAME,
    Rec_ID, CREATED,
    ROW_NUMBER() OVER (PARTITION BY Rec_ID 
                       ORDER BY (SELECT 01)) AS rn
FROM   
    #DEL_FAC
WHERE  
    CREATED = 'User_1';

Output:

ID      Name                        Rec_ID       Created    RN
----------------------------------------------------------------      
15755   MARBLE MFG                  1032          User_1    1
17186   COMPRESSOR                  1033          User_1    1
15711   Bakery Solutions            1052          User_1    1
15931   BAKERY SOLUTIONS - FROZEN   1052          User_1    2
16182   Ceramics Inc                 128          User_1    1
16509   E-Z-G                      12811          User_1    1
17836   E-Z-Go, Company            12811          User_1    2
15940   LANDFILL                    1321          User_1    1
15182   LANDFILL- MAINT             1321          User_1    2
16291   Landfill -3                 1321          User_1    3

SQLChao · Accepted Answer · 2017-01-09 17:04:50Z

3

You could do something like

   SELECT ID
 , NAME
 , Rec_ID
 , CREATED
 , ROW_NUMBER() OVER(PARTITION BY Rec_ID ORDER BY
                    (
                        SELECT 01
                    )) AS rn
FROM   #DEL_FAC
WHERE  CREATED = 'User_1'
  AND rec_id in (select rec_id from #dec_fac
                 group by rec_id
                 having count(rec_id) > 1)

answered Jan 9, 2017 at 17:04

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TJB · Accepted Answer · 2017-01-09 17:03:45Z

3

You don't need to do any partitioning, you can just obtain the list by use of HAVING doing a count on Rec_ID:

SELECT Rec_ID
FROM #DEL_FAC
WHERE CREATED = 'User_1'
GROUP BY Rec_ID
HAVING COUNT(Rec_ID) > 1;

answered Jan 9, 2017 at 17:03

TJB

8872 gold badges13 silver badges30 bronze badges

1 Comment

Ryan B. Over a year ago

OP wants the detail tho, so this would need to be part of a subquery returning the list of IDS to select from the original dataset.

Gordon Bell · Accepted Answer · 2017-01-09 17:06:20Z

3

Or...

select Name, Rec_ID, Created from #DEL_FAC
where Rec_ID in
(
    select Rec_ID from #DEL_FAC
    where Created = 'User_1'
    group by Rec_ID
    having count(*) > 1
)

answered Jan 9, 2017 at 17:06

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

Comments

Alessandro Rossi · Accepted Answer · 2017-01-09 17:14:49Z

1

You can just put an inner WHERE clause so you can select all the rows having User_1 as CREATED and the Rec_ID belonging to the set of multiple Rec_IDs

SELECT ID, NAME, Rec_ID, CREATED FROM #DEL_FAC
   WHERE  CREATED = 'User_1' AND Rec_ID IN (
     SELECT Rec_ID 
     FROM #DEL_FAC
     GROUP BY Rec_ID
     HAVING COUNT(*) > 1 
)

answered Jan 9, 2017 at 17:14

Alessandro Rossi

311 bronze badge

Collectives™ on Stack Overflow

Selecting only rows with multiple entries in SQL Server

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related