### Thread: How to order closest to specific number?

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. In essence you need to create a calculated field in your query

PHP Code:
``` abs(x - 250) as `nearest`  ```
and then sort by this field.
3. 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
4. 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!
5. 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.
6. 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.
7. 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);`
8. 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";`
9. You're ordering by d.faults first. Don't do that.
10. 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?
11. 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