Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Help needed to generate a "sort code"

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I should have read more carefully!

Posting Permissions

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