Results 1 to 2 of 2

Thread: Relevancy

  1. #1
    Join Date
    Dec 1969

    Default Relevancy

    I have a query where I need to rank by relevant matches. The query is simple<BR>something like:<BR><BR>Select first_name, last_name, addr FROM people WHERE first_name LIKE<BR>&#039;$VARIABLE$&#039; OR last_name LIKE...etc.<BR><BR>How do I rank the results by most relevant columns? For example if there was<BR>a match in the first,last and addr column it would be highest, if there was<BR>a match only in addr it would be lower.<BR><BR>Thanks.<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Relevancy

    How about spliting it up into 3 queries at first and just selecting the (ID field I&#039;m assuming there is one!)<BR><BR>(Select ID where first_name Like &#039;$Variable$&#039;)<BR>UNION ALL<BR>(Select ID where last_name like &#039;$Variable$&#039;)<BR><BR>Then group by ID select count and order by the count:<BR><BR>Select Matches.ID, count(Matches.ID) as theCount<BR>From (<BR>(Select ID where first_name Like &#039;$Variable$&#039;)<BR>UNION ALL<BR>(Select ID where last_name like &#039;$Variable$&#039;)<BR>) as Matches<BR>Group by Matches.ID<BR>Order by theCount<BR><BR>You&#039;ll probably have to play around with this a little, but its an idea to get you started

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts