Distinct Statement Mystery

  • 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

  • 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].

    T

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

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