Hi,

Perhaps my questions wasn't as simple as I had expected/hoped

I'll describe in more detail the siuation I am in

Currently I am developing an application which imports data from a firebird
database, which is connected to a proprietary application
I cannot write to the table (well I could, but everytime the application
starts the datamodel is reset)
I need to do a select on one table and filter the specific records I need
(the duplicates)
The table has 11.000 records
When I do the subquery I get a CPU load of 100% and it takes forever. since
this will be done daily currently there are insufficient resources for that.

The table looks like this (still simplisticly)

people
ID,Name,AssignedValue
1,A,1
2,B,1
3,C,2
4,D,1
5,E,2
6,F,3
I want my output to be:
ID,Name,AssignedValue
1,A,1
2,B,1
4,D,1
3,C,2
5,E,2

Currently I have
SELECT a.ID,a.Name,a.Assignedvalue FROM Persons a WHERE a.Assignedvalue IN
(SELECT b.Assignedvalue FROM Persons b GOUP BY b.Assignedvalue HAVING
COUNT(b.assignedVALUE) > 1)
ORDER BY a.Assignedvalue

This works, just it takes forever
I tried Embedded, Superserver and Classic
All hog the cpu, the difference lies in the memory usage (16,8,3)MB

Is there a better way to do this?

Thanks in advance everyone

On Wed, Jul 30, 2008 at 1:28 PM, ruan_h <leohuanruan@...> wrote:

> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> "Serge Fonville"
> <serge.fonville@...> wrote:
> >
> > Hi,
> >
> > Thanks for the repsonse
> >
> > Unfortunately, this is just a simple join finding the fields together.
> > This way the textual skillevel for each individual is listed
> > What I need is that only the duplicates are shown individually
> >
> > Serge
> >
> > On Wed, Jul 30, 2008 at 1:15 PM, Lester Caine <lester@...> wrote:
> >
> > > serge.fonville wrote:
> > > > Hi,
> > > >
> > > > I have two tables:
> > > > persons:
> > > > ID,name,skillLevelID
> > > > 1,John,4
> > > > 2,George,4
> > > > 3,Caroline,2
> > > > 4,Harold,3
> > > > 5,Arnold,2
> > > >
> > > >
> > > > SkillLevel:
> > > > ID,Name
> > > > 1,The Best
> > > > 2,Good
> > > > 3,Sufficent
> > > > 4,Reasonable
> > > > 5,Not that good
> > > > 6,Bad
> > > > 7,Awful
> > > >
> > > > The output should be
> > > > Skilllevels:
> > > > Person,level
> > > > John,reasonable
> > > > George,reasonable
> > > > Caroline,Good
> > > > Arnold,Good
> > >
> > > Simple
> > >
> > > SELECT a.NAME, b.NAME
> > > FROM PERSONS a
> > > JOIN SKILLLEVEL b ON a.SKILLLEVELID = b.ID
> > >
> > > --
> > > Lester Caine - G8HFL
> > > -----------------------------
> > > Contact - http://lsces.co.uk/lsces/wiki/?page=contact
> > > L.S.Caine Electronic Services - http://lsces.co.uk
> > > EnquirySolve - http://enquirysolve.com/
> > > Model Engineers Digital Workshop - http://medw.co.uk//
> > > Firebird - http://www.firebirdsql.org/index.php
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> Maybe try this:
>
> select
> pl.id,
> pl.name,
> skill.skilldesc
> from people pl
> inner join skill on skill.skillid = pl.skillid
> where
> exists (
> select 1
> from people pl1
> where pl1.skillid = pl.skillid and
> pl1.id <> pl.id
> )
>
>
>


[Non-text portions of this message have been removed]