How to order closest to specific number?

Dev Shed Forums Sponsor:

November 23rd, 2012, 07:17 PM

coffeeaddict

Registered User

Join Date: Nov 2012

Posts: 6

Time spent in forums: 4 h 46 m 37 sec
Reputation 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?

November 23rd, 2012, 07:32 PM

AndrewSW

JavaScript is not spelt java

Join Date: Feb 2011

Location: Landan, England

Posts: 743

Time spent in forums: 2 Weeks 1 Day 22 h 56 m
Reputation Power: 164

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.

November 23rd, 2012, 07:50 PM

coffeeaddict

Registered User

Join Date: Nov 2012

Posts: 6

Time spent in forums: 4 h 46 m 37 sec
Reputation 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

November 23rd, 2012, 07:53 PM

AndrewSW

JavaScript is not spelt java

Join Date: Feb 2011

Location: Landan, England

Posts: 743

Time spent in forums: 2 Weeks 1 Day 22 h 56 m
Reputation Power: 164

Quote:

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!

November 23rd, 2012, 07:56 PM

coffeeaddict

Registered User

Join Date: Nov 2012

Posts: 6

Time spent in forums: 4 h 46 m 37 sec
Reputation 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.

November 23rd, 2012, 08:26 PM

AndrewSW

JavaScript is not spelt java

Join Date: Feb 2011

Location: Landan, England

Posts: 743

Time spent in forums: 2 Weeks 1 Day 22 h 56 m
Reputation Power: 164

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.

November 24th, 2012, 08:34 AM

ManiacDan

Likely to be eaten by a grue.

Dev Shed God 10th Plane (9500 - 9999 posts)

Join Date: Oct 2006

Location: Pennsylvania, USA

Posts: 9,791

Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 2 Months 3 Weeks 14 h 53 m 20 sec
Reputation Power: 6112

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.

November 30th, 2012, 11:07 AM

coffeeaddict

Registered User

Join Date: Nov 2012

Posts: 6

Time spent in forums: 4 h 46 m 37 sec
Reputation 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";