in reply to Re: [OT] Weighted Search Results
in thread [OT] Weighted Search Results

Brilliant idea. I poped into MySQL Control Center and discovered two things. You need parenthesis around the whole forumula and then you also need to use having instead of where. Where wont work with column aliases and having will.

SELECT *,  ((State = 'CA') + (City = "Chico")) AS score  FROM `Address
+`
       HAVING score > 0
       ORDER BY score DESC

[download]

___________
Eric Hodges

Replies are listed 'Best First'.
Re: Re: Re: [OT] Weighted Search Results
by CiceroLove (Monk) on May 20, 2004 at 17:36 UTC
    This worked like an absolute champ! What a wonderful day this has turned out to be! As was noted by a colleague of mine, this of course coudl slow down dramatically for large datasets since the having is an unindexed column and woudl require some shuffling by MySQL in the background.


    CiceroLove
    Fates! We will know your pleasures: That we shall die, we know; 'Tis but the time, and drawing days out, that men stand upon. - Act III,I, Julius Caesar
[reply]
      For larger cases you could break it into to parts. First get all records that have at least one matching feild. Then go threw those and score them. You could use a temp table or nested select statments.

      ___________
      Eric Hodges
[reply]

In Section Seekers of Perl Wisdom