Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Select 'A and B' records first, then 'A or B' records

    Hi everyone,

    I have a dynamically generated query containing any number of OR clauses. Let's say I just have one : select A or B. I want SQL to select all records which contain A or B STARTING WITH those which contain both!

    If I have two OR clauses, like SELECT A or B or C , then I want SQL to select first and foremost the records which include A and B and C as well, then those which include A and B, those which include B and C and those which include A and C. Then, those which include only A, those which include only B, and those which include only C.

    You get the idea. But how to do that ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT foo
         , CASE WHEN foo like 'A%' THEN 1 ELSE 0 END +
           CASE WHEN foo like 'B%' THEN 1 ELSE 0 END +
           CASE WHEN foo like 'C%' THEN 1 ELSE 0 END   AS score
      FROM daTable
    ORDER
        BY score
         , foo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    Works perfectly. Thank you.

Posting Permissions

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