Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Question Unanswered: Ordering results by number of conditions met

    I have a query that pulls records from a table based on whether is matches one or more of several criteria. I'd like to be able to order the results of this query by the number of conditions a particular row met. So a row that met 3 conditions would rank about a row that only met 2. So far I haven't been able to dent this with any attempt so I'm open to any suggestions.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may work, I haven't tried, but it may

    order by case
    when (field1=condition1 and field2=condition2 and field3=condition3) then 3
    when (field1=condition1 and field2=condition2) then 2
    when (field1=condition1) then 1
    else 0
    end
    desc
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    7
    Query executes but doesn't appear to have an effect on the ordering.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Place the CASE statement into the SELECT part and see what rating is being assigned.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2004
    Posts
    7
    It looks like it is working for cases where the condition = column, but if I try something with 'like' with wildcards (%) then that case never occurs.

  6. #6
    Join Date
    Jul 2004
    Posts
    7
    Problem solved had the wildcards to the left of the like, switched them to the right, problem solved. thanks!

Posting Permissions

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