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!!!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
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.
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.
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.
ASKER
SELECT * FROM main WHERE (PK IN (SELECT DISTINCT SUB.PK FROM (SELECT main.* FROM mainTable INNER JOIN ..... INNER JOIN ... )SUB))