SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best match from the rows

    Hi,

    I need a way to get the best match of some conditions in WHERE clause.

    The WHERE clause has about 8 different conditions and from the rows, I want to get the results GROUPED BY only those which are BEST MATCH for the given condition.

    For example, look at the table below:

    tbl.gif

    And I give query like :

    Code:
    SELECT * from users WHERE  age < 25 OR marks > 190
    and this SHOULD show:

    Row: 1 and 2 as the ages are < 25 and marks are > 190. However it MUST not show Row 4, because YES the age is < 25 but marks is NOT > 190.

    I know that I can do it by using "AND" instead of "OR" but in my table there are about 30 columns and I must use "OR" to get the results. Only thing is that I want the BEST POSSIBLE ROWs that matches most of the conditions in the where clause.

    Please help !

    Regards
    ZH

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    one way to do it is to assign each criterion a "match" score, then sort by the sum of scores
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
         , CASE WHEN age < 25    THEN 2 ELSE 0 END +
           CASE WHEN marks > 190 THEN 1 ELSE 0 END AS score
      FROM users 
     WHERE age < 25 
        OR marks > 190
    ORDER
        BY score DESC
    in the example above, i've used a higher score for age meeting its criterion than for marks meeting its criterion

    whether you discard the rows not having a high enough score using sql or in your application program is up to you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy !

    That would be the solution - thanks once again.

    I can put as much CASE WHEN as possible and it will keep adding to "score" for a particular row ? Can I give 1 point for each matched condition ? like :

    CASE WHEN age < 25 THEN 1 ELSE 0 END +
    CASE WHEN marks > 190 THEN 1 ELSE 0 END +
    CASE WHEN blah = blah THEN 1 ELSE 0 END
    AS score

    This way, if all the conditions are matched - it will score 3. ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by zeeshanhashmi View Post
    I can put as much CASE WHEN as possible and it will keep adding to "score" for a particular row ?
    yes, that's the idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.

    I do not have as much SQL knowledge as you have, but last night I was on bed and thinking of something that can do this scoring thing.


Bookmarks

Posting Permissions

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