#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    How to order closest to specific number?


    I have a project I'm working on and I'm stuck on how to order results closest to a specific number.

    I have a breed of dog with an ideal number. We will say 250

    For that breed I have 5 different dogs all that have their own set of numbers. I then added those together and now have a single total for each of those 5 dogs.

    Out of those 5 numbers, I need to order them by the closest to 250.

    Now some may be below 250 or above 250.

    Lets say my totals for these 5 dogs were

    240
    310
    122
    109
    87


    That right there is how I would want them ordered. Because 240 is closest to 250 and so on.

    I know how to order by ascending or desceding, but not how to order according to a specific number. Help please?
  2. #2
  3. JavaScript is not spelt java
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2011
    Location
    Landan, England
    Posts
    743
    Rep Power
    165
    In essence you need to create a calculated field in your query

    PHP Code:
    abs(250) as `nearest
    and then sort by this field.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    That makes sense! Thank you!

    I have 40 different breeds, is there a shorter way to go about this for each one, other than typing out the code 40 times? Probably not, and it's not really a lot of code, but can't hurt to ask. Thanks again
  6. #4
  7. JavaScript is not spelt java
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2011
    Location
    Landan, England
    Posts
    743
    Rep Power
    165
    I have 40 different breeds, is there a shorter way to go about this for each one, other than typing out the code 40 times?
    I don't understand why you would need to type the code 40 times - that's what variables, functions, and loops are for!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Because each breed has a different number. So it's a different calculation for each one.

    I am assuming I would need to go through and define the ideal numbers for each breed in the database, then add up the totals for each dog of those breeds, then find the difference between each dog's number and their ideal, and then order it. Correct?

    But that finding the ideal number would have to be done 40 different times, right? Or am I missing something that's probably staring right at me?

    Edited to add: I think I figured it out. I was thinking I would have to do a bunch of if statements for each breed. If doberman then ideal is 240, if collie then ideal is 400, etc. etc.

    But I see now I don't need to do that.
  10. #6
  11. JavaScript is not spelt java
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2011
    Location
    Landan, England
    Posts
    743
    Rep Power
    165
    I don't quite follow, but if you had another table listing the 40 breeds and their corresponding ideal value (or this information could be created and stored in a temporary array) then a query to obtain the data you need (for all 40 breeds) would be something like the following:

    Code:
    SELECT Breeds.Breed, Dogs.Dog, Sum(Abs([A]+[ B ]+[C]-[Ideal])) AS TotPerDog FROM Breeds INNER JOIN Dogs ON Breeds.Breed = Dogs.Breed GROUP BY Breeds.Breed, Dogs.Dog ORDER BY Sum(Abs([A]+[ B ]+[C]-[Ideal])) DESC;
    (without the extra spaces around B)
    N.B. this is an Access query, but the SQL will be similar.
  12. #7
  13. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    In MySQL, assuming you have:
    DOG: ID, BREED_ID, NAME, VALUE, ETC
    BREED: ID, NAME, IDEAL_VALUE

    Code:
    SELECT DOG.NAME, BREED.NAME FROM DOG JOIN BREED ON DOG.BREED_ID =  BREED.ID ORDER BY BREED.NAME, ABS(DOG.VALUE-BREED.IDEAL_VALUE);
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    I have been trying to test this out and would like some feedback on this snipit of code. So far, it doesn't quite seem to be working. Dogs that are not closest to their ideal are still being ordered first. I'm quite sure it's something I'm doing wrong, any suggestions are appreciated!

    genetotal is the ideal number

    Code:
    $query .= "(D.head + D.ears + D.topline + D.front + D.rear + D.feet + D.health + D.temperament)) AS genetics,  					B.group  			  FROM entries E  			  INNER JOIN dogs D ON D.id = E.dog_id  			  INNER JOIN breeds B on B.id = D.breed  			  WHERE  			  		E.show_id = '{$show['id']}' AND  			  		('{$show['classification']}' = 1 OR E.nc = 1) 			  ORDER BY  			  		D.faults ASC,  					level DESC,  					ABS(genetics-B.genetotal) DESC";
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    You're ordering by d.faults first. Don't do that.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Dogs with faults need to be automatically put last, I didn't think that would be causing the problem. Hmmm.

    Everything else looks ok though?
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    Just wanted to update this and say it's working perfectly, I had to order the ABS by ASC. Thanks both of you who replied

IMN logo majestic logo threadwatch logo seochat tools logo