SELECT DISTINCT single column

Posted on 2007-08-02
Last Modified: 2012-05-05
If I have a complex SQL statement like SELECT * FROM .... INNER JOIN .... INNER JOIN .... etc

The query returns a bunch of fields, including NTEXT.  I *NEED* a distince in there, but that fails since  you cannot put a distinct on NText.  Is there any way to apply distinct to a SINGLE COLUMN, ie, tell it not to duplicate field X (which is my primary key) but not worry about anything else?

Question by:ARACK04
    LVL 92

    Accepted Solution

    ARACK04 said:
    >> Is there any way to apply distinct to a SINGLE COLUMN, ie, tell it not to duplicate
    >>field X (which is my primary key) but not worry about anything else?

    I do not think you can do this.  NTEXT cannot be part of SELECT DISTINCT, nor can
    it be in a GROUP BY clause.
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    If you just need to take the DISTINCT of a particular column, then instead of * use that particular column along with a DISTINCT keyword.
    Now, if you want to take the Distinct of the text column, then , you can convert that field to a Varchar / nvarchar column and can apply the Distinct

    SELECT DISTINCT CAST(urTextColumn AS varchar(8000) )
    FROM urTable
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    to further clarify the issue:
    say you have 3 rows returned for the same PK value, which one of the 3 do you want to get actually returned?

    as noted by aneeshattingcal, you can start using the CAST() function to change the NEXT into VARCHAR.
    --> now, are you using sql 2005 as your question is posted in that zone?
      -> yes: change the NTEXT column in NVARCHAR(MAX) data type
      -> no:  do you really need more that 4000 characters for that field?
      ->      do you really need that field in this select output?

    Author Comment

    I found a workaround - something along the lines of:

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    just to note that the DISTINCT there is double work, you do NOT need it that way.

    you might note that an exists clause will work better than this syntax.

    Author Comment

    exists is a good idea.

    I don't see how DISTINCT is double work though.  I'm reading the data set from the big JOIN, then getting a distinct list of primary keys, then re-loading the data just from the actual main DB Table corresponding to these keys.

    Yeah, it's some extra work for the DBMS, but I have tested similar cases extensively, and found that the weakest link is almost always dragging the result set across the network, versus having the DBMS do some extra work.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now