using distinct pulls data from column not referenced

[charanch]

I originally posted this in the ASP forum, but they suggested I try here. This is a weird one. I have a sql 2000 table with telephone billings from sept-oct. When I execute the following statement, I get the time column mixed in with the phone number column. When I remove the word distinct from the query, I get only the phone numbers. What's up with that? I checked the data many times just to make sure there were only phone numbers in the phone number column and it's all correct and accurate.

SELECT DISTINCT ([Phone Number])
FROM Oct212004

When DISTINCT is removed, only the phone numbers are returned, just like it's supposed to be. I've used GROUP BY, and IS NOT NULL, I've exhausted my ideas. NONE of the other columns do that when I use DISTINCT. Any thoughts? Thanks.

 

[SQLSister]

Is that your whole query? OR are there some other fields in the query?

I see no reason at all why the above query should not work.

However with other fields distinct often does not gove the results intended because it applies to all columns not just the one you might want distinct values for.

Questions about posting. See faq183-874

 

[donutman]

I think you found a new feature
Try all sorts of variations like:

[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] YourTimeColumn
[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Phone Number] [green]--without ( )
[/green][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Phone Number][Gray],[/Gray] SomeOtherColumn
[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Phone Number][Gray],[/Gray][Phone Number]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[charanch]

That really is my entire query. Just a simple little query. There is a user phone number field in the same table and it doesn't screw that one up. I'll keep messing around with it and see if I can get it working. I even went back as far as april that one is ok. I think I'll reimport it. It was originally imported from a spreadsheet.

 

[SQLSister]

Very odd error.

This is way more complicated than it would normally be but try
Select distinct [Phone Number]
from
(SELECT ([Phone Number])
FROM Oct212004) a

Questions about posting. See faq183-874