Link to home
Start Free TrialLog in
Avatar of Erwin Pombett
Erwin PombettFlag for Switzerland

asked on

in T-SQL, how can i recover all rows that have a column that appear more than once ?

Hello,

i've a table with columns a, b, c, d

how can recover all the rows where b appear more that once.

having a result with the columns a, b, c, d and a complementary columns "d" that has the number of occurences ?

thank you in advance.

toshi
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>where b appear more that once.
>and a complementary columns "d" that has the number of occurences ?
Explain for us at Barney-level what you mean here, as the above two statements seem to contradict each other.

Even better would be a data mockup both before and after.
try:
;With D
As
(
Select row_number() over (partition By b Order By b) As rn,
       a
	 , b
	 , c
	 , d
From #t 
)
Select a, b, c, d, rn-1 As Repeats from d;

Open in new window


It returns:
1       b        c         d            Repeats             
1      10      100      1000      0
1      10      100      1000      1
1      10      100      1000      2
1      11      100      1000      0
1      12      100      1000      0
1      13      100      1000      0
1      14      100      1000      0

Using:
CREATE TABLE #t(
	[a] [int] ,
	[b] [int] ,
	[c] [int] ,
	[d] [int]
) 

insert into #t(a,b,c,d) values
  (1,10,100,1000)
, (1,10,100,1000)
, (1,11,100,1000)
, (1,10,100,1000)
, (1,12,100,1000)
, (1,13,100,1000)
, (1,14,100,1000);

select * from #t;

Open in new window

Avatar of Erwin Pombett

ASKER

Hello eghtebas,

thanks a lot for your query.  
could you help me to understand ?

i'm trying to figure out how this is working ?

thank you in advance.

toshi
Jim Horn,

thank you to make me discover "Barney" ;)  You were right, i wasn't clear enough.
Next time i 'll follow your tip: data mockup both before and after.

luckyly eghtebas read on my mine.

toshi
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
on of the best answers i ever received.
i asked for explanation and i received them ;)
thank you so much.
toshi