Help needed to generate a "sort code"

kenicheema · #1 (**permalink**) 10-25-04, 14:07

I have been puzzled by this over the last 2 days, maybe an SQL expert here can help me out.

I have the following table:
==============
Pref
==============
UID int
SMOKE bit
LIVESMOKE bit
PET bit
LIVEPET bit
==============

I have a record with the following preferences:
smoke: 1
live with smoker: 1
pet: 0
live with pet: 0

how can I write an SQL statement to pull up all rows in the table sorted by the number of matching preferences to the row indicated above?

My initial thought was to xnor the pref which will return 1 on each matching pref but then I still need to count the number of on bit on the xnor result.

Have anyone done this before? any idea or suggestion?

r937 · #2 (**permalink**) 10-25-04, 18:07

Code:

order
    by case when SMOKE     = 1 then 1 else 0 end 
      +case when LIVESMOKE = 1 then 1 else 0 end 
      +case when PET       = 0 then 1 else 0 end 
      +case when LIVEPET   = 0 then 1 else 0 end 
     desc

andrewst · #3 (**permalink**) 10-26-04, 05:35

I'm not used to the BIT datatype. So you can't do this then?

order by SMOKE+LIVESMOKE+PET+LIVEPET

i.e. BITs are not implicitly converted to INTEGERS by an expression like that?

r937 · #4 (**permalink**) 10-26-04, 07:36

not implicitly, no, you actually have to use CAST on them

however, that won't help here, as the problem was to find rows with most matches to the particular combination 1, 1, 0, 0

andrewst · #5 (**permalink**) 10-26-04, 07:39

I should have read more carefully!

Internet Services

Web Development

Digital Marketing

Consumer Tech