MS SQL Server New Users - Select Distinct problem

Author

Select Distinct problem

Aleks

2005-10-27, 7:55 am

Hi, I have the query below, the problem is that it returns duplicate results
(with same a.id's)

I need to add a 'distinct' clause so that I don't get repeated results for
the a.id

---------------

SELECT a.id, a.caseid, a.firmid, a.maincase,b.comid, b.comments,
b.Updatedby, b.LastUpdate, h.MailStr, h.MaidenNm, d.LastNm,
d.Nistatus, d.ExpiresOn ,d.FirstNm,d.I94dateD, d.I797date,d.I797,
a.expdate,a.approvaldate,a.archived, e.processcatalog, f.MaidenNm as
EmployerNm, l.JobTitle, w.processtep, w.dateinitiated, w.acttype
FROM cases a left join casecomments as b on a.id = b.caseid AND
b.lastupdate = (SELECT MAX(x.lastupdate) FROM casecomments x WHERE
x.caseid=a.id) left join activities as w on a.id = w.caseid AND
w.Dateinitiated = (Select MAX(x.Dateinitiated)
FROM Activities x Where x.caseid = a.id and w.acttype = 'HISTORY')
left join users as f on a.empid = f.userid left join users as d
on a.alienid = d.userid left join employment as L on
d.userid=L.userid and l.curemp = 1 inner join processcatalog as
e ON e.processcatalogid = a.process left join users as h on
b.Updatedby = h.userid
WHERE a.firmid = MMColParam AND a.archived LIKE 'MMColParam1'
ORDER BY EmployerNm, d.LastNm, a.caseid,a.maincase

------------------

I tried by simply adding the word 'distinct' like: SELECT a.id, a.caseid,
a.firmid ... etc

But the problem is some fields are text and I get an error because of that.
How can I change the query so that it checks for dups ONLY for the a.id
field ?

Thanks for the help.

Alejandro

Aleks

2005-10-27, 7:55 am

I tried this but did not work, I get the same error messg.

SELECT distinct(a.id), a.caseid, a.firmid, a.maincase,b.comid, b.comments,
b.Updatedby
... etc ..

--------------------------

"Aleks" <arkark2004@hotmail.com> wrote in message
news:ulPgyA3zFHA.3588@tk2msftngp13.phx.gbl...
> Hi, I have the query below, the problem is that it returns duplicate
> results (with same a.id's)
>
> I need to add a 'distinct' clause so that I don't get repeated results for
> the a.id
>
> ---------------
>
> SELECT a.id, a.caseid, a.firmid, a.maincase,b.comid, b.comments,
> b.Updatedby, b.LastUpdate, h.MailStr, h.MaidenNm, d.LastNm,
> d.Nistatus, d.ExpiresOn ,d.FirstNm,d.I94dateD, d.I797date,d.I797,
> a.expdate,a.approvaldate,a.archived, e.processcatalog, f.MaidenNm as
> EmployerNm, l.JobTitle, w.processtep, w.dateinitiated, w.acttype FROM
> cases a left join casecomments as b on a.id = b.caseid AND
> b.lastupdate = (SELECT MAX(x.lastupdate) FROM casecomments x WHERE
> x.caseid=a.id) left join activities as w on a.id = w.caseid AND
> w.Dateinitiated = (Select MAX(x.Dateinitiated)
> FROM Activities x Where x.caseid = a.id and w.acttype = 'HISTORY') left
> join users as f on a.empid = f.userid left join users as d on
> a.alienid = d.userid left join employment as L on
> d.userid=L.userid and l.curemp = 1 inner join processcatalog
> as e ON e.processcatalogid = a.process left join users as h
> on b.Updatedby = h.userid
> WHERE a.firmid = MMColParam AND a.archived LIKE 'MMColParam1'
> ORDER BY EmployerNm, d.LastNm, a.caseid,a.maincase
>
> ------------------
>
> I tried by simply adding the word 'distinct' like: SELECT a.id, a.caseid,
> a.firmid ... etc
>
> But the problem is some fields are text and I get an error because of
> that. How can I change the query so that it checks for dups ONLY for the
> a.id field ?
>
> Thanks for the help.
>
> Alejandro
>

Aleks

2005-10-27, 7:55 am

It is similar to this post, in my case I only want to use the first field
for the distinct, how can I do this ?

-------------

got the following error . May i know how to do "distinct" on text field ?

1> SELECT DISTINCT s.id, s.new, s.modified, s.deleted, s.archived,
s.published,
s.approved, s.submitted, s.checkedOut, s.aclId, s.parentId, s.ordering,
s.templa
te, s.publishVersion, w.className, w.version, w.name, w.description,
w.summary,
w.author, w.date, w.related FROM cms_content_status s LEFT JOIN
cms_content_work
w ON s.id=w.id
2> go
Msg 421, Level 16, State 2:
Server 'LOCALHSOT', Line 1:
TEXT, IMAGE, UNITEXT and off-row Java datatypes may not be selected as
DISTINCT.
1>

-------------

"Aleks" <arkark2004@hotmail.com> wrote in message
news:ulPgyA3zFHA.3588@tk2msftngp13.phx.gbl...
> Hi, I have the query below, the problem is that it returns duplicate
> results (with same a.id's)
>
> I need to add a 'distinct' clause so that I don't get repeated results for
> the a.id
>
> ---------------
>
> SELECT a.id, a.caseid, a.firmid, a.maincase,b.comid, b.comments,
> b.Updatedby, b.LastUpdate, h.MailStr, h.MaidenNm, d.LastNm,
> d.Nistatus, d.ExpiresOn ,d.FirstNm,d.I94dateD, d.I797date,d.I797,
> a.expdate,a.approvaldate,a.archived, e.processcatalog, f.MaidenNm as
> EmployerNm, l.JobTitle, w.processtep, w.dateinitiated, w.acttype FROM
> cases a left join casecomments as b on a.id = b.caseid AND
> b.lastupdate = (SELECT MAX(x.lastupdate) FROM casecomments x WHERE
> x.caseid=a.id) left join activities as w on a.id = w.caseid AND
> w.Dateinitiated = (Select MAX(x.Dateinitiated)
> FROM Activities x Where x.caseid = a.id and w.acttype = 'HISTORY') left
> join users as f on a.empid = f.userid left join users as d on
> a.alienid = d.userid left join employment as L on
> d.userid=L.userid and l.curemp = 1 inner join processcatalog
> as e ON e.processcatalogid = a.process left join users as h
> on b.Updatedby = h.userid
> WHERE a.firmid = MMColParam AND a.archived LIKE 'MMColParam1'
> ORDER BY EmployerNm, d.LastNm, a.caseid,a.maincase
>
> ------------------
>
> I tried by simply adding the word 'distinct' like: SELECT a.id, a.caseid,
> a.firmid ... etc
>
> But the problem is some fields are text and I get an error because of
> that. How can I change the query so that it checks for dups ONLY for the
> a.id field ?
>
> Thanks for the help.
>
> Alejandro
>

Hugo Kornelis

2005-10-27, 7:55 am

On Wed, 12 Oct 2005 18:11:59 -0400, Aleks wrote:

>I tried this but did not work, I get the same error messg.
>
>SELECT distinct(a.id), a.caseid, a.firmid, a.maincase,b.comid, b.comments,
>b.Updatedby
> ... etc ..

Hi Aleks,

DISTINCT always applies to the complete row.

If you want one row for each value of a.id, you'll have to define how
the other values are selected. You could use the lowest value for each:

SELECT a.id, MIN(a.caseid), MIN(a.firmid), ...
FROM ... AS a
WHERE ...
GROUP BY a.id

But the results might be from multiple rows. If you want one row, you'll
have to do some more work - and it can only be done if you have a way to
uniquely determine exactly one row for a give a.id.

Let's assume that the combination of a.id and a.caseid is unique in your
data, then the following query would select the row that goes with the
lowest a.caseid for each a.id:

SELECT a.id, a.caseid, a.firmid, ...
FROM ... AS a
INNER JOIN (SELECT id, MIN(caseid) AS MinCaseID
FROM ...) AS aMin
ON aMin.id = a.id
AND aMin.MinCaseID = a.caseid
WHERE ...

Or, as an alternative (test both for performance, if performance matters
in your situation):

SELECT a.id, a.caseid, a.firmid, ...
FROM ... AS a
WHERE ...
AND NOT EXISTS
(SELECT *
FROM ... AS aOther
WHERE aOther.id = a.id
AND aOther.caseid < a.caseid)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links