query to display non distinct values of a column in table?

Question

Post reply

query to display non distinct values of a column in table?

royston_pledger

Ten Centuries

Points: 1031
More actions
June 11, 2008 at 7:06 am

#188952

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

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]

SQL-4-Life

royston_pledger Ten Centuries Points: 1031 More actions · Answer 2

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

royston_pledger Ten Centuries Points: 1031 More actions · Answer 3

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

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]

SQL-4-Life

royston_pledger Ten Centuries Points: 1031 More actions · Answer 5

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:)