on 30/4/01 15:42, Frank Schima at macsforever2000 AT yahoo D.OT com wrote:
>> the first form I did like much more.
>> because in second case it needs to do a lots of string comparison.
>>
>> may be
>>
>> Select RecID, ID, NAME From MyTable
>> Where RecID in
>> (Select RecID, NAME From MyTable Group By NAME Having count(*) > 1)
>> Order By NAME
>
> Here's the problem with that. In the sub-query, you must only return one
> column because that is the list used to search for, in this case, the RecID.
> You cannot compare RecID and NAME (apples and oranges!!). Sybase would not
> even allow this to run.
>
> The reason I changed it to NAME in the first place is because you are
> "supposed" to always at least Select the Group By clause. It is not required,
> but weird results can occur if you don't. When I think about it, it makes
> sense. Say I Group By NAME like I tried the first time and ask for the ID. The
> result set should only have one record for each NAME if there are 2 or more.
> So which ID does it return? We want both (or 3 or more) but we have only one
> record with one ID column.
Okay I see, then what about this:
Select RecID, ID, NAME From MyTable
Where RecID in
( Select RecID FROM
(Select RecID, NAME From MyTable Group By NAME Having count(*) > 1)
)
Order By NAME
--
Best regards,
Ruslan Zasukhin
-------------------------
Paradigma.
e-mail: ruslan@paradigmasoft.com
web : http://www.paradigmasoft.com
To subscribe to the Valentina mail list
send a letter to valentina-on AT lists D.OT macserve.net
-------------------------------------------------------------
The Valentina mailing list is brought to you by MacServe.net
For info on lists services, see http://www.macserve.net/lists.html
©2001 Ruslan Zasukhin |
