[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SELECT DISTINCT single column

Posted on 2007-08-02
6
Medium Priority
?
3,050 Views
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?

Thanks!!!
0
Comment
Question by:ARACK04
6 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 800 total points
ID: 19622499
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.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 800 total points
ID: 19622501
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
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 19622699
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?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:ARACK04
ID: 19622802
I found a workaround - something along the lines of:

SELECT * FROM main WHERE (PK IN (SELECT DISTINCT SUB.PK FROM (SELECT main.* FROM mainTable INNER JOIN ..... INNER JOIN ... )SUB))
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19622838
>WHERE (PK IN (SELECT DISTINCT SUB.PK
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.
0
 

Author Comment

by:ARACK04
ID: 19622880
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.  
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 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