View previous topic :: View next topic
|
Author |
Message |
learnmf
Active User
Joined: 14 Mar 2005 Posts: 123
|
|
|
|
Hi Friends
I have nother query.
How to find the rows of a Table which contain only duplicate values.
Thanks
Chandra |
|
Back to top |
|
|
notonly4u
New User
Joined: 26 Apr 2005 Posts: 87 Location: Hyderabad
|
|
|
|
Use Count(var-name) > 1 in the Where or Having clause.
Please correct me If Iam wrong...
Regards
Tanden |
|
Back to top |
|
|
parikshit123
Active User
Joined: 01 Jul 2005 Posts: 269 Location: India
|
|
|
|
Hi,
SELECT * FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*)
> 1 ;
Here are the duplicate rows in a relation student :
studentid | studentname
-----------+-------------
102 | mital
103 | dmitk
Hope it helped. |
|
Back to top |
|
|
karthi_ind
Active User
Joined: 24 Feb 2004 Posts: 131 Location: Chennai
|
|
|
|
Hi Parishit
The query u hve mentioned will give error a
Group is inconsistent.
If u give any coln after Group by , u shld mention thse cols in the
select statement so the query shld be
SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ;
Correct me if i m wrong.
Karthi G. |
|
Back to top |
|
|
parikshit123
Active User
Joined: 01 Jul 2005 Posts: 269 Location: India
|
|
|
|
Hi,
My assumption was to have only two columns in the table that I have used.
If all the columns in the table are used in the group by clause, then I think we can use * in the query as mentioned above |
|
Back to top |
|
|
iknow
Active User
Joined: 22 Aug 2005 Posts: 411 Location: Colarado, US
|
|
|
|
Hi learnmf,
Here is an easy to follow technique.
Assuming a table TABLE_X with column_1, column_2, and column_3, and assuming the aim is to find all columns who happen to have exact duplicate combination of data for these three columns: You would key, in the SQL command line the following command:
Code: |
SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
HAVING COUNT(*) > 1
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3 |
NOTE
What Karthi_ind mentioned is right but in his query he has interchanged GROUP BY and HAVING. I am not sure whether it works. |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
iknow...
Quote: |
SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
HAVING COUNT(*) > 1
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3 |
Was that tested before posting as a reply to the question. Try to run the query you posted.
HAVING Clause is not used like that....
Quote: |
NOTE
What Karthi_ind mentioned is right but in his query he has interchanged GROUP BY and HAVING. I am not sure whether it works. |
What Karthi_ind mentioned is correct.... HAVING is used with GROUP BY clause only....
Regards,
Priyesh. |
|
Back to top |
|
|
appasi
New User
Joined: 12 Dec 2005 Posts: 20 Location: Bangalore
|
|
|
|
Hi iknow,
What Karthi_ind mentioned is right ,I am sure whether it works.
try it , Correct me if i m wrong. |
|
Back to top |
|
|
iknow
Active User
Joined: 22 Aug 2005 Posts: 411 Location: Colarado, US
|
|
|
|
Hi All,
What karthi_ind suggested is works fine.
Karthi_ind wrote:
Quote: |
SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ; |
Mine is showing error in HAVING CLAUSE.
Sorry for the inconvenience. |
|
Back to top |
|
|
anilbatta
New User
Joined: 13 Nov 2005 Posts: 22
|
|
|
|
Hi
Inorder to find the rows of a table which are having the duplicate values, the following is the query in which table is having with columns as ID, NAME in a table called STUDENT.
SELECT ID, NAME FROM STUDENT HAVING COUNT(*)>1
GROUP BY ID, NAME; |
|
Back to top |
|
|
gireeshsk
New User
Joined: 20 Dec 2005 Posts: 6 Location: chennai
|
|
|
|
SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ;
The Querry works fine |
|
Back to top |
|
|
gireeshsk
New User
Joined: 20 Dec 2005 Posts: 6 Location: chennai
|
|
|
|
hi fiends
how can i delete a duplicate records from the table retaining the original row(atleast one row of the duplicates)
should i have to use rowid |
|
Back to top |
|
|
|