Jump to content

help with ORDER BY LEAST()


aebstract

Recommended Posts

$query = mysql_query("
SELECT registrations.firstname, registrations.lastname, participants.q1, participants.q2, participants.q3, participants.q1s, participants.q2s, participants.q3s, participants.id, participants.cr
FROM events
INNER JOIN participants ON participants.eventid = $_GET[event]
INNER JOIN registrations ON registrations.id = participants.regid
WHERE events.id = $_GET[event] AND registrations.class = '$var'
ORDER BY LEAST(q1, q2, q3)") or DIE(mysql_error());

 

 

This seems to work.. UNLESS the number is double digits. So if it's 9.999 it will order correctly with the other results, but if the number for q1, q2, q3 is 10.000 or greater, it puts it above lower numbers.

Link to comment
Share on other sites

Here is my current query:

$query = mysql_query("
SELECT registrations.firstname, registrations.lastname, participants.q1, participants.q2, participants.q3, participants.q1s, participants.q2s, participants.q3s, participants.id, participants.cr
FROM events
INNER JOIN participants ON participants.eventid = $_GET[event]
INNER JOIN registrations ON registrations.id = participants.regid
WHERE events.id = $_GET[event] AND registrations.class = '$var'
ORDER BY LEAST(q1, q2, q3)") or DIE(mysql_error());

Sorry for the double post, here is what I need to do:

 

I have a race class like 7.00 Index. You are aiming to get as close to 7.00 as possible without going under. For qualifying (q1,q2,q3 = qualifying rounds 1-3) if someone get's under 7.00, say 6.98 they would go to the bottom of the list. Right now they obviously go to the top. Though, if they come back in round 2 of qualifying and post a 7.045, it should move them up according to the 7.045 number. I have no clue how to go about getting this to work and really hope someone can help me out quickly. I have to have this ready in the next hour or two.

 

Other problem with query: If I put a double digit such as 21.705 it will move them up above everyone else as if their number is lowest..

Link to comment
Share on other sites

If it's varchar, MySQL sorts it as text, which means all numbers starting with '1' will be before any numbers starting with '2' which in part will be before any numbers starting with '3'.

 

If you wan't numeric sorting, use numeric column like DECIMAL or FLOAT

Link to comment
Share on other sites

I backed up my table, changed to float. Seems some of my information has been changed to 0.000. I can probably go back and manually enter that stuff back in. Now my problem is similar to what I posted in other thread. If the time is a 0 it shouldn't count in the order by, because that's not a valid qualify time. A 0 means they didn't run a qualify lap and shouldn't be put in qualify position 1 because of it. The other thing is I need to order it to a specific number then flip and continue from that number down. Example:

 

7.001

7.024

7.321

7.579

8.168

21.547

6.987

6.575

6.235

 

This would be a valid result order. With three qualifying times the only time that the numbers under 7.0 should be at the bottom is if either all three qualifying times are under 7.0 or the ones entered are and the others are either 0 or not entered or w/e

Link to comment
Share on other sites

Certainly will try to get you whatever will help.

 

Firstly, here is a link to the section I'll be referring to:

http://outlawracing.com/index.php?page=ladders&event=1

If you scroll down you will see 4.70 Index, 5.30 Index, 6.00 Index and 7.00 Index

 

The order that I'm getting right now is from the query I mentioned above, here it is again:

 

$query = mysql_query("
SELECT registrations.firstname, registrations.lastname, participants.q1, participants.q2, participants.q3, participants.q1s, participants.q2s, participants.q3s, participants.id, participants.cr
FROM events
INNER JOIN participants ON participants.eventid = $_GET[event]
INNER JOIN registrations ON registrations.id = participants.regid
WHERE events.id = $_GET[event] AND registrations.class = '$var'
ORDER BY LEAST(q1, q2, q3)") or DIE(mysql_error());

 

 

For example, I'll do the 4.70 class.

 

Here is what it currently looks like off of the above query:

Mike Herring  4.616 @ 151.46  4.669 @ 154.03  4.686 @ 153.23

Blake Wilder  4.685 @ 151.34  5.161 @ 128.65  5.023 @ 109.83

Scott Underwood  4.730 @ 149.18  4.706 @ 150.45  4.706 @ 150.45

Kenny Acree  4.730 @ 148.85  4.730 @ 148.85  4.730 @ 148.85

Isreal Deraney  4.794 @ 148.87  4.732 @ 149.13  4.732 @ 149.13

Randy Alexander  4.822 @ 137.48  4.755 @ 148.20  4.755 @ 148.20

Jeff Wilkerson  5.026 @ 116.73  4.902 @ 145.47  4.902 @ 145.47

Jody Voyles  [/td]  4.973 @ 140.18  4.973 @ 140.18

Jeremy Ellis  5.044 @ 133.8  5.044 @ 133.83  5.044 @ 133.83

Mike Ghee    5.045 @ 137.79  5.045 @ 137.79

 

 

It needs to be ordered like this:

Scott Underwood  4.730 @ 149.18  4.706 @ 150.45  4.706 @ 150.45

Kenny Acree  4.730 @ 148.85  4.730 @ 148.85  4.730 @ 148.85

Isreal Deraney  4.794 @ 148.87  4.732 @ 149.13  4.732 @ 149.13

 

Randy Alexander  4.822 @ 137.48  4.755 @ 148.20  4.755 @ 148.20

Jeff Wilkerson  5.026 @ 116.73  4.902 @ 145.47  4.902 @ 145.47

Jody Voyles    4.973 @ 140.18  4.973 @ 140.18

Blake Wilder  4.685 @ 151.34  5.161 @ 128.65  5.023 @ 109.83Jeremy Ellis  5.044 @ 133.83  5.044 @ 133.83  5.044 @ 133.83

Mike Ghee  5.045 @ 137.79  5.045 @ 137.79

Mike Herring  4.616 @ 151.46  4.669 @ 154.03  4.686 @ 153.23

 

 

Now to explain it. It's for a drag racing qualifications page. (I'll be using something similar on a pairings tree also)

In a 4.70 Index class, each car is racing to get as close to a 4.70 E.T. as possible. So you're going for as quick of a time as possible, but trying to not go under 4.70 but as close to it as possible. If you go under it and none of your other qualifying round times are above it, then your highest (closest to 4.70) time would be counted but put behind all other racers who hit over 4.70. So anyone under 4.70 it kind of flips to where you want the highest (still as close to 4.70) and if you're over it where you should be, you want the lowest ET. Though, if you go under let's say a 4.65 and then on another run do a 4.71, your 4.71 would be what counts as it is your "best time". I think the above example has a little bit of all of these examples in it.

 

The last racer, mike herring didn't get a round over 4.70, so 4.686 was his best since it was closest to 4.70.

Blake Wilder ran a 4.685 in round 1, but since he ran over a 4.70 in round 3 (5.023) that is the score that will count. This places him above mike herring who only got times under 4.70.

 

I hope this helps on clarifications. Thanks for your time!

Link to comment
Share on other sites

Yeah, I am sure that it is and it would have a few little quirks in it, but I've never done anything such as conditional statements inside of a query. I had a deadline of last Saturday but now I think I have until next Monday to figure it out.

Link to comment
Share on other sites

So basically, you just want to take the LEAST amongst the values where the ABSOLUTE value of the difference of the value and 4.7?

 

That doesn't seem like that to me. It's more like 'take the smallest of those larger than 4.7, and if there are none, take the largest of those smaller than 4.7'

Link to comment
Share on other sites

Which is the same as the smallest value of absolute value of the difference of the value and 4.7, right?

 

In a time line:

 

---------------------|------------------

                        4.7

 

It really depends on how far away you are from 4.7. So using the absolute value should work right? Or am I misunderstanding this?

Link to comment
Share on other sites

Which is the same as the smallest value of absolute value of the difference of the value and 4.7, right?

 

In a time line:

 

---------------------|------------------

                        4.7

 

It really depends on how far away you are from 4.7. So using the absolute value should work right? Or am I misunderstanding this?

 

No. If you have 4.69, 4.72 and 4.75 then 4.72 is taken (even though 4.69 is closer to 4.7)

Link to comment
Share on other sites

If anything, I'd move the times to separate table

 

table results

----------------------

result_id, participant_id, run, time

1,  1,  1,  4.76

2,  1,  2,  4.78

3,  1,  3,  4.72

4,  2,  1,  4.68

5,  2,  2,  4.73

6,  2,  3,  4.75

....

 

Then

 

SELECT 
  p.participant_id, IF(above.time IS NOT NULL, above.time, below.time)
FROM
  participants AS p
LEFT JOIN
  (SELECT participant_id, MIN(time) AS time WHERE time >= 4.7 GROUP BY particpant_id) AS above
USING (participant_id)
LEFT JOIN
  (SELECT participant_id, MAX(time) AS time WHERE time < 4.7 GROUP BY particpant_id) AS below
USING (participant_id)

 

(not tested)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.