How can we select duplicate rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can we select duplicate rows in a table

Author

Topic

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-02 : 18:46:05

Table1
A......B...................................

1......Mouse................................
2......Keyboard.............................
3......Printer..............................
2......Keyboard.............................
1......Mouse................................

1st .
I want to select all those rows which are duplicate for example in Table1 it will be like

1......Mouse................................
1......Mouse................................
2......Keyboard.............................
2......Keyboard.............................

2nd.
Select all those rows which are not duplicate for example in Table1 it will be like

3......Printer..............................

plz guide me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-02 : 19:52:35

1. SELECT A, B, COUNT(*) FROM Table1 GROUP BY A, B HAVING COUNT(*) > 1
2. SELECT A, B FROM Table1 GROUP BY A, B HAVING COUNT(*) = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-03 : 18:56:18

quote:
Originally posted by tkizer

1. SELECT A, B, COUNT(*) FROM Table1 GROUP BY A, B HAVING COUNT(*) > 1
2. SELECT A, B FROM Table1 GROUP BY A, B HAVING COUNT(*) = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Tara Kizer
The solution you have written just tells us how many rows are duplicate it does not show all duplicate rows for example the solution you have written shows result if we apply on above table1
1.
A.......B............. (No columne Name)
2.......KeyBoard...... 2
1.......Mouse......... 2
but i want it should shows like
A......B
1......Mouse................................
1......Mouse................................
2......Keyboard.............................
2......Keyboard.............................

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-03 : 18:58:35

You can use a derived table using query1 to get your output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-03 : 19:29:15

Take look at 'Using the FROM Clause' in books online.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-03 : 20:02:43

Seeing as though you posted this question in both the 2000 and 2005 forums, we need to know what version you have.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-04 : 04:28:13

Tara Kizer
I am using sql 2000.I am sorry i have put this topic on 2005 too.Now i have known my forum is this.I am sorry again for this.
my problem wan not solved yet. the solution you have written just tells us how many rows are duplicate.It does not take out all duplicate rows.i was in a big trouble bcoz of this i have put this topic on 2005 forum. i am sorry for that.actually i need the solution urgently.plz tell me anthor solution.
plz Note i am beginner in sql so plz tell me solutoin as easy as possible.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 04:45:02

Try this

SELECT t1.* FROM Table1 as t1 INNER JOIN
(
SELECT A, B, COUNT(*) FROM Table1 GROUP BY A, B HAVING COUNT(*) > 1
) as t2 on t1.A=t2.A and t1.B=t2.B

Madhivanan

Failing to plan is Planning to fail

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-04 : 06:48:32

Madhivanan
I have tried this but it gives the following error
No column was specified for column 3 of 't2'.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 06:50:59

you need a column alias for the count(*) in the subquery...

SELECT t1.* FROM Table1 as t1 INNER JOIN
(
SELECT A, B, COUNT(*) as C FROM Table1 GROUP BY A, B HAVING COUNT(*) > 1
) as t2 on t1.A=t2.A and t1.B=t2.B

Em

RoniDxb
Starting Member

28 Posts

Posted - 2008-08-04 : 07:16:11

Yessssss it works
Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 08:34:18

quote:
Originally posted by elancaster

you need a column alias for the count(*) in the subquery...

SELECT t1.* FROM Table1 as t1 INNER JOIN
(
SELECT A, B, COUNT(*) as C FROM Table1 GROUP BY A, B HAVING COUNT(*) > 1
) as t2 on t1.A=t2.A and t1.B=t2.B

Em

Thanks. I forgot to specify it. I always name it as counting

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources