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

gilbutre · #1 (**permalink**) 02-23-10, 04:07

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 ?

r937 · #2 (**permalink**) 02-23-10, 04:47

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

gilbutre · #3 (**permalink**) 02-23-10, 08:33

Works perfectly. Thank you.

Internet Services

Web Development

Digital Marketing

Consumer Tech