June 11, 2008 at 7:06 am
Hi,
In a table, out of 250,000 records only 248,245 records are distinct (a result of some process done on the values). I need to display/select the values that are not distinct...
I think there is no NOT DISTINCT in sql 2000..
Can you help with the select query?
Thanks
Roy
June 11, 2008 at 7:14 am
hi,
This is a basic query:
SELECT *
FROM MyTable
WHERE ID IN
(SELECT ID
FROM MyTable
GROUP BY ID
HAVING COUNT(*) >1)
The nested select can be changed to a Join/Derived table if you wish, I just wrote it like this as it's easier to read.
Hope this helps
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 11, 2008 at 7:54 am
Christopher Stobbs (6/11/2008)
hi,This is a basic query:
SELECT *
FROM MyTable
WHERE ID IN
(SELECT ID
FROM MyTable
GROUP BY ID
HAVING COUNT(*) >1)
Chris,
I imported a text file having 250,000 unique names(text has 250000 lines..ie.1 name per line) to a table MYTABLE(with 1 column..NAME) in sql server. The names include values like Ada,AAda,Adae....
Have a look at the results for the following queries performed on the table MYTABLE..
Query1:
select count(distinct NAME) from MYTABLE
The result was...248025
Query2:
SELECT * FROM MYTABLE WHERE NAME IN(SELECT NAME FROM MYTABLE GROUP BY NAME HAVING COUNT(*) =1)
This returned 246050 records
Query3:
SELECT * FROM MYTABLE WHERE NAME IN(SELECT NAME FROM MYTABLE GROUP BY NAME HAVING COUNT(*) >1)
This returned 3950 records
I searched with these values in the text file(imported)..but their no of occurence is 1...no repetition
Query1 and Query2 seem to contradict each other..though they meant the same..
Am i missing something ??
Roy
June 11, 2008 at 8:08 am
I imported a text file having 250,000 unique names(text has 250000 lines..ie.1 name per line) to a table MYTABLE(with 1 column..NAME) in sql server. The names include values like Ada,AAda,Adae
Could have something gone wrong when importing the text file....
I used DTS import/export wizard on SQL 2000 with..
Row Delimiter : {LF}
Text identifier: none
column delimiter: Others[0]
Roy
June 11, 2008 at 8:09 am
mmm
Ok lets see if I can explain this.
SELECT COUNT(DISTINCT NAME)
This will return the count of the distinct names regardless of duplicates
e.g
ROW NAME
1 A
2 A
3 B
This returns a count of 2
However with the other query this returns a count of Names that are distinct rather than the count of the names themselves
e.g
ROW NAME
1 A
2 A
3 B
This returns 1 (because only 1 name has a count of 1).
Does this make sense?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 11, 2008 at 11:02 pm
Yep...got it!
But my other concern was i checked for duplicate values in the file containing the names(using a java program)....but could not find any...
Anyway, i found the culprit:
The java program checked for duplicates....considering the case sensitiveness!
The sql query did not check for case sensitiveness!
For example: Abc and ABc are the same according to the query.
Thanks chris...that select query that you gave helped me to identify the duplicate values:)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy