Link to home
Start Free TrialLog in
Avatar of ARACK04
ARACK04

asked on

SELECT DISTINCT single column

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!!!
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ARACK04
ARACK04

ASKER

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))
>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.
Avatar of ARACK04

ASKER

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.