--- Frank Schima <macsforever2000 .at. yahoo DOT com> wrote:
> --- Ruslan Zasukhin <sunshine@public.kherson.ua> wrote:
> > on 30/4/01 15:42, Frank Schima at macsforever2000 .at. yahoo DOT 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
>
> Very slick! Yes, that could work. And you are correctly illustrating how to use Group By with a
> different Select column set. The innermost sub-query is valid in Sybase.
>
> However the issue now is if you can replace a table in a From statement with a sub-query. Even
> Sybase cannot do this. However, if Valentina did, it would be *great* because I have wanted this
> ability this for a long time!
I was playing around with this some more and discovered an optimization. Your first SQL above was
very close to working. So I modified it and came up with this:
Select RecID, ID, NAME From MyTable
Where RecID in
(Select RecID From MyTable Group By NAME Having count(*) > 1)
Order By NAME
Note how the sub-query only Selects RecID now. I guess I learned something here!
-Frank
=====
Frank Schima <mailto:macsforever2000@yahoo.com>
Yahoo Messenger Chat <macsforever2000>
INADEV Consulting Inc. <http://www.inadev.com/>
Columbia, MD USA <http://columbiamaryland.com/>
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
-------------------------------------------------------------
The Valentina mailing list is brought to you by MacServe.net
For info on lists services, see http://www.macserve.net/lists.html
©2001 Frank Schima |
