Return DISTINCT Values

[obrienkev]

Hi,

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

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.

[azimmer]

Hi,

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

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]

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]

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]

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]

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]

Tried the below query but returns duplicate rows...

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]

Tried the below query but returns duplicate rows...

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:

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]

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]

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?)