So is 2.06 ready yet?

Relax, put your pistols back in their holsters!!! I was just having a little fun!

My real question ...

Is there a better way to write this?

Need a distinct list while displaying multiple fields.

SELECT C.DisplayName,MIN(C.ClientID),MIN(C.FirstName),MIN (R.Description) FROM Clients C
LEFT JOIN ClientRolodex CR ON (C.ClientID = CR.ClientID)
INNER JOIN Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE C.IsRep = True
AND R.Description = 'Securities Rep.'
GROUP BY C.DisplayName;

Regards,
Scott.

Another thought .. I would like to make a call like such as I need all the fields,
but I want to distinct only 1. Of course this will not resove. Is there another
approach?

For now, I guess I can create constant for the field names

Regards,
Scott.

SELECT C.DisplayName,MIN(C.*) FROM Clients C
LEFT JOIN ClientRolodex CR ON (C.ClientID = CR.ClientID)
INNER JOIN Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE C.IsRep = True
AND R.Description = 'Securities Rep.'
GROUP BY C.DisplayName;

"Scott Martin" <scottmartin@pdq.net> wrote in message news:4526635a@wic040d....
>
> Need a distinct list while displaying multiple fields.
>
> SELECT C.DisplayName,MIN(C.ClientID),MIN(C.FirstName),MIN (R.Description) FROM Clients C
> LEFT JOIN ClientRolodex CR ON (C.ClientID = CR.ClientID)
> INNER JOIN Rolodex R ON (CR.RolodexID=R.RolodexID)
> WHERE C.IsRep = True
> AND R.Description = 'Securities Rep.'
> GROUP BY C.DisplayName;
>
> Regards,
> Scott.
>

"Scott Martin" <scottmartin@pdq.net> wrote in message
news:4526635a@wic040d....
> Relax, put your pistols back in their holsters!!! I was just having a
> little fun!

I - for one - am perfectly relaxed 2.06 is ready when it's ready.

> My real question ...
>
> Is there a better way to write this?
>
> Need a distinct list while displaying multiple fields.

Not really. I assume you mean without the MIN() expression, but without
something like that, there would be no way in general for the engine to know
which ClientID or FirstName you would want to show for a particular
DisplayName. Maybe they're all identical, but the engine can't know that
before the fact.

- Per

> SELECT C.DisplayName,MIN(C.ClientID),MIN(C.FirstName),MIN (R.Description)
> FROM Clients C
> LEFT JOIN ClientRolodex CR ON (C.ClientID = CR.ClientID)
> INNER JOIN Rolodex R ON (CR.RolodexID=R.RolodexID)
> WHERE C.IsRep = True
> AND R.Description = 'Securities Rep.'
> GROUP BY C.DisplayName;
>
> Regards,
> Scott.
>