help with ORDER BY LEAST()

[aebstract]

$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.

[Mchl]

Are q1,q2,q3 numeric columns, or are they VARCHAR by any chance?

[aebstract]

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..

[aebstract]

Right now they are varchar, I am up to changing anything that needs to be changed. I started a new thread to explain a little better and put two issues I have in the same post. Sorry for this. Thanks for looking in to this!

[Mchl]

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

[aebstract]

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

[aebstract]

bump

[Mchl]

What you describe is very confusing. Could you perhaps show some source data and how should it be ordered?

[aebstract]

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!

[aebstract]

bump

 

[aebstract]

anything at all?

[Mchl]

That's quite confusing logic. I didn't have much time to take a deeper look into it, but it doesn't seem impossible to me.

[aebstract]

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.

[aebstract]

Morning bump. I've been working on a few other side things and will be trying to jump in to this head on soon.

[aebstract]

bump

 

[fenway]

bump

That's quite enough bumping for now.

[aebstract]

I was quite sure that over 24 hours would be plenty of time, what would the time frame be?

[Ken2k7]

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?

[Mchl]

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'

[Ken2k7]

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?

[aebstract]

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'

 

This seems to be correct.

[Ken2k7]

Oh, sorry, I misunderstood. I'll try to come up with something.

[Mchl]

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)

[Ken2k7]

What about UNION? Just UNION those two cases applying the formula I suggested earlier.

[Mchl]

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)