partial DISTINCT...

Hi,
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID =
tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.

> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
>
of
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>