SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    total weight order

    Code:
    data in myTable
    
    id name   country     city   sex
    1  john    America   Denver  male
    2  Mary    England   London  female
    3  Judy    France    Chicago   female
    4  Tom     German    Berlin  male
    5  Carol   America   Paris   female
    6  Jane    America   Tokyo   female
    I have data in myTable like the above.

    when I have America(weight 80%), Paris(weight(50%), and male(weight 20%) as 3 keywords,
    I like to output like the below.
    Code:
    target result
    
    (1) Carol  // 80%+50%=150%(total weight)
    (2) John // 80%+20%=120%
    (3) Jane // 80%
    (4) Tom  // 20%
    (5) Mary  // 0% 
    (6) Judy  // 0% Judy comes later than Mary because of "ORDER BY ID"
    The following code doesn't work correctly, but I hope it shows what I want.
    Code:
    would-be code
    
    select name from myTable
    where 
    weight(country,80%)='America'
    or
    weight(city,50%)='Paris'
    or
    weight(sex,20%)='male'
    order by ID

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    The following code doesn't work correctly
    not even close

    is that really your best attempt, after all the hundreds of times i have helped you with SQL???

    Quote Originally Posted by dotJoon View Post
    but I hope it shows what I want.
    well, sort of

    i don't understand why you have a WHERE clause, because it looks like you want all rows returned

    furthermore, you neglected to mention where the percentages are stored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is that really your best attempt ...?
    Not really, Soorry, I might focus on the showing my target instead of making the real code.

    Quote Originally Posted by r937 View Post
    , after all the hundreds of times i have helped you with SQL???
    I forgot, I am afraid, many parts of your point.
    I work in another field for money although my dream is in this field.

    Quote Originally Posted by r937 View Post
    i don't understand why you have a WHERE clause, because it looks like you want all rows returned
    Neither do I.
    As I found that I should put the weight phrase in ORDER clause, I can't modify the OP because of sitePoint's preventing "EDIT" by time-passing.

    Quote Originally Posted by r937 View Post
    furthermore, you neglected to mention where the percentages are stored
    percentages could be Dynamic.
    Server language Page can give the percentages Dynamically. or DataBase can give it.

    Let's suppose that Database has the percentTable like the below.
    Code:
    data in percentTable
    
    (myField)  percent
    
    country       80
    city          50
    sex           20

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    after all the hundreds of times i have helped you with SQL???
    Quote Originally Posted by JOON View Post
    I forgot, I am afraid, many parts of your point.
    I work in another field for money although my dream is in this field.

    I have to add one more thing,

    I've never asked about weight before.
    This question is the first time relating weight.

    Come on, Rudy, please...


Bookmarks

Posting Permissions

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