Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct Statement Mystery Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 10:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:40 PM
Points: 33, Visits: 88
I have a db of Contacts - it's real simple, NAME, ADDRESS, CITY, ST, ZIP, etc...


I CAN DO

SELECT ST
FROM TABLE


and I get all of the ST from every record.

When I try
SELECT DISTINCT ST
FROM TABLE

it displays what looks like a complete listing of DISTINCT CITY values

but it is UNDER THE COLUMN HEADING 'ST'

It's like the values next to ST (CITY) has replaced the desired query for some reason,
any suggestions?

thx
Spatio






Post #1592402
Posted Tuesday, July 15, 2014 1:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 1,708, Visits: 4,514
fgrubercpa (7/14/2014)
I have a db of Contacts - it's real simple, NAME, ADDRESS, CITY, ST, ZIP, etc...


I CAN DO

SELECT ST
FROM TABLE


and I get all of the ST from every record.

When I try
SELECT DISTINCT ST
FROM TABLE

it displays what looks like a complete listing of DISTINCT CITY values

but it is UNDER THE COLUMN HEADING 'ST'

It's like the values next to ST (CITY) has replaced the desired query for some reason,
any suggestions?

thx
Spatio


Quick questions,
a. can you post the ddl for the table
b. what do you get from the following query
SELECT 
T.ST
,ROW_NUMBER() OVER (PARTITION BY T.ST ORDER BY (SELECT NULL)) AS ST_RID
FROM [schema].[TABLE] T

Post #1592423
Posted Tuesday, July 15, 2014 9:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:40 PM
Points: 33, Visits: 88
Running the T-SQL you gave, I got 2 columns entitled ST and SR_RID

under ST there seems to be a mix of cities combined with the standard 2 letter postal abbrev for states , i.e. FL, GE MN, ETC...


Some of the records even returned Addresses under the ST Column, in addition to some having the City, as opposed to state info.


If somehow how I imported this wrong and the data was placed in the the wrong columns in some fields, I could understand, but doing SELECT *, I can view all the records and the ST Column shows only the 2 Ltr Postal Abbrevs. So ????

I apologize, I did not understand what you meant by my 'ddl'.
Thanks
Spatio

Post #1592637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse