Return DISTINCT Values

**azimmer** · Aug 22 '07, 03:19 PM

Originally posted by obrienkev

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below??

Code:

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

What do you want the other fields be? First/Last/Any/Other? I mean that if you return the r.GPositionID, some values need to get associated with CommentImage, GCustodian, GCustodianAccou nt, and GAssetType as well.

**obrienkev** · Aug 22 '07, 03:53 PM

Originally posted by azimmer

What do you want the other fields be? First/Last/Any/Other? I mean that if you return the r.GPositionID, some values need to get associated with CommentImage, GCustodian, GCustodianAccou nt, and GAssetType as well.

r.GPositionID,G Custodian,GCust odianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

thanks.

**azimmer** · Aug 22 '07, 06:41 PM

Originally posted by obrienkev

r.GPositionID,G Custodian,GCust odianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

thanks.

If you want all distinct combinations put the DISTINCT keyword after SELECT, i.e.: SELECT DISTINCT CommentImage AS ViewComment,r.G PositionID,GCus todian,GCustodi anAccount,GAsse tType
...

If it's not what you want, give us examples, pls.

**obrienkev** · Aug 23 '07, 08:42 AM

Originally posted by azimmer

If you want all distinct combinations put the DISTINCT keyword after SELECT, i.e.: SELECT DISTINCT CommentImage AS ViewComment,r.G PositionID,GCus todian,GCustodi anAccount,GAsse tType
...

If it's not what you want, give us examples, pls.

Yes I want to do the above query but SQL Server gives this error...
The text, ntext, or image data type cannot be selected as DISTINCT

CommentImage field has datatype of image.

Thanks.

**azimmer** · Aug 24 '07, 07:00 AM

Originally posted by obrienkev

Yes I want to do the above query but SQL Server gives this error...
The text, ntext, or image data type cannot be selected as DISTINCT

CommentImage field has datatype of image.

Thanks.

That's bad but understandable (on SQL's side): what do you mean by distinct images? If you do mean they have to be different you'll have to find a workaround. If you don't, put all of your select but the image one into an "inner" select in the FROM clause, join the "inner" select with the table that contains the image and in an "outer" select select (all) fields from the "inner" select and the image from the other one.
Hope it helps. Without knowing the table structure and your exact specs it's hard to be more specific.

**obrienkev** · Aug 24 '07, 08:58 AM

Tried the below query but returns duplicate rows...

Code:

SELECT cm.CommentImage AS ViewComment, r.GPositionID,r.GCustodian,r.GCustodianAccount,r.GAssetType
FROM @GResults r
LEFT OUTER JOIN
	RComments cm
	ON cm.GPositionID = r.GPositionID
AND cm.GPositionID = (SELECT min(GPositionID)
		      FROM RComments cm
		      WHERE GPositionID = r.GPositionID)
order by r.GPositionID

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccou nt varchar(250),
GAssetType varchar(250)

**azimmer** · Aug 24 '07, 02:16 PM

Originally posted by obrienkev

Tried the below query but returns duplicate rows...

Code:

SELECT cm.CommentImage AS ViewComment, r.GPositionID,r.GCustodian,r.GCustodianAccount,r.GAssetType
FROM @GResults r
LEFT OUTER JOIN
	RComments cm
	ON cm.GPositionID = r.GPositionID
AND cm.GPositionID = (SELECT min(GPositionID)
		      FROM RComments cm
		      WHERE GPositionID = r.GPositionID)
order by r.GPositionID

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccou nt varchar(250),
GAssetType varchar(250)

My suggestion:

Code:

SELECT CommentImage AS ViewComment,
		r.GPositionID,GCustodian,
		GCustodianAccount,GAssetType
FROM (
      SELECT DISTINCT
		GPositionID,GCustodian,
		GCustodianAccount,GAssetType
      FROM @GResults
	 ) r
     LEFT OUTER JOIN ReconComments cm
		ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN
	(SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;

**obrienkev** · Aug 27 '07, 08:55 AM

Returned rows from RComments table of the same GPositionID.

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccou nt varchar(250),
GAssetType varchar(250)

Thanks.

**azimmer** · Aug 27 '07, 12:52 PM

Originally posted by obrienkev

Returned rows from RComments table of the same GPositionID.

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccou nt varchar(250),
GAssetType varchar(250)

Thanks.

Especially if it's an M:M relation (technically the same as N:M), my problem is the same as SQL Server's: we'd need to compare and distinguish images... (It seems that you want DISTINCT images -- that's what the core of the problem is, isn't it?)