[thelist] SQL In need of Optimization....

[John Pye]

Mmm respect to that solution of yours Rudy! Very tidy.

An aside question: would that method generalise nicely for use in a
relevance-ranking search system, do you think, and would it be efficient?

I was thinking of something like the following, working on something like
this for a system I'm working on at the moment. (Might be better to get
stuck into InterMedia Text on the other hand)


SELECT articleid,articlename,relevance
FROM articles
WHERE (articleid,relevance) IN
 (SELECT articleid,count(*) as relevance
  FROM articlemetadata
  WHERE
   (metadataname='author'
    AND NLS_UPPER(metadatavalue) CONTAINS NLS_UPPER('Adams')
   )
   OR
   (metadataname='mortalitystatus'
    AND NLS_UPPER(metadatavalue) CONTAINS NLS_UPPER('Alive')
   )
  GROUP BY articleid
  HAVING relevance>1
 )


John Pye
http://www.creativebase.com/
+44 (0)20 7258 6900



-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of rudy
Sent: Friday, June 29, 2001 3:20 PM
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL In need of Optimization....


replying to my own post again [sheesh]

>   select obladi, oblada
>     from tblCollegeSearch
>   where CollegeID in
>      ( select CollegeID , count(*)
>           from tblCollegeMajors
>         where MajorID in ( #majorList# )
>         group by CollegeID
>           having count(*) >= #X# )

the subquery should return only the CollegeID -- fundamental syntax error
to have more than one column if the subquery is used for an IN test

anthony, if you ask me now to generate the hit count (how many majors does
each college offer from among the ones that were checked off), that's a lot
harder and it's too nice a day for heavy sql...

rudy