Jump to content

help with ORDER BY LEAST()


aebstract

Recommended Posts

What you just posted really got me confused. Advanced queries do that. If I go any sort of route that requires me changing the DB, then I'm also looking at having to change a lot of code for stuff that already exists. I could MAYBE do the 3 tables just for qualifying and just for the classes that require this special query, and leave the rest alone. *shrugs*

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)

 

Could you explain this? It looks like you're calling to 2 new tables, but said to add 1 and show 1 at the top. I think I can work with something like this as a workaround if I understand it a little more. Thanks

Link to comment
Share on other sites

Which is what I have already, I just have the information stored on 1 row instead of multiple. So instead of making a new table like you said, just change the method to 1 row per round per user?

 

id | eventid | userid | round# | rt | et | speed

 

have a unique id, event id, user id, round number, reaction time, et, and speed for each row.

What does above. and below. refer to?

Link to comment
Share on other sites

'above' and 'below' are aliases for two subqueries used here.

(SELECT participant_id, MIN(time) AS time WHERE time >= 4.7 GROUP BY particpant_id) AS above
...
(SELECT participant_id, MAX(time) AS time WHERE time < 4.7 GROUP BY particpant_id) AS below

Link to comment
Share on other sites

The code you posted, won't return a result like this:

123

4.764.784.72

4.68 4.734.75

 

I can see it checking each one or whatever but don't see how it brings the results based on the three rounds for each. I might be looking at it wrongly. I set up a separate table just for these that are done in this way. I have id, regid, eventid, round, et, rt, mph.

 

regid = registered users id

eventid = event id

round = which round, possible options being: q1, q2, q3

et = decimal

rt = decimal

mph = decimal

 

The round time should actually be et+rt, but that shouldn't be hard to add in the end if I need to.

 

I just did this:

$query = mysql_query("SELECT
  p.regid, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  participants AS p
LEFT JOIN
  (SELECT regid, MIN(et) AS et WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et WHERE et < 4.7 GROUP BY regid) AS below
USING (participant_id)") or DIE(mysql_error());

while($row=mysql_fetch_array($query)){

echo "$row[et]";

}

 

just to see what I get, this query is going to have to be more specific as in class and event id, but with just this I got an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE et >= 4.7 GROUP BY regid) AS above USING (regid) LEFT JOIN (SELECT regid' at line 6

 

Link to comment
Share on other sites

Yeah, this query only returns a time that should be taken into consideration for ranking, it does not do any ranking though.

As for the syntax error, I see nothing. Make sure there is no unnecessary comma (,) before WHERE

Link to comment
Share on other sites

Oh wait... there's no FROM clause in these subqueries

$query = mysql_query("SELECT
  p.regid, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  participants AS p
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM results WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM results WHERE et < 4.7 GROUP BY regid) AS below
USING (participant_id)") or DIE(mysql_error());

 

how come I made a msitake like this?

Link to comment
Share on other sites

Okay, I've got this query working sort of:


$query = mysql_query("SELECT
  i.regid, i.eventid, i.et, r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  index_q as i
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 GROUP BY regid) AS above
USING (regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 GROUP BY regid) AS below
USING (regid)
LEFT JOIN registrations AS r  ON r.id = i.regid
WHERE r.class = '$var' && i.eventid = '$_GET[event]'") or DIE(mysql_error());

if(mysql_num_rows($query)!=0){

	while($r=mysql_fetch_array($query))
	{
$content .= "$r[firstname] $r[lastname] $r[et]<br />";
	}
						}

 

With an output of this:

Kenny Acree 4.730

Blake Wilder 4.685

Jeremy Ellis 5.044

Randy Alexander 4.822

Scott Underwood 4.730

Isreal Deraney 4.794

Jody Voyles 0.000

Mike Ghee 0.000

Mike Herring 4.616

Jeff Wilkerson 5.026

Kenny Acree 4.730

Blake Wilder 5.161

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.669

Jeff Wilkerson 4.902

Kenny Acree 4.730

Blake Wilder 5.023

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.686

Jeff Wilkerson 4.902

 

This doesn't seem to have any ordering to it though, and is there any way to loop through each person individually so I can put their times beside eachother when I loop through?

 

firstname lastname et1 et2 et3

 

 

Link to comment
Share on other sites

[quote[Kenny Acree 4.730

Blake Wilder 4.685

Jeremy Ellis 5.044

Randy Alexander 4.822

Scott Underwood 4.730

Isreal Deraney 4.794

Jody Voyles 0.000

Mike Ghee 0.000

Mike Herring 4.616

Jeff Wilkerson 5.026

 

Kenny Acree 4.730

Blake Wilder 5.161

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.669

Jeff Wilkerson 4.902

 

Kenny Acree 4.730

Blake Wilder 5.023

Jeremy Ellis 5.044

Randy Alexander 4.755

Scott Underwood 4.706

Isreal Deraney 4.732

Jody Voyles 4.973

Mike Ghee 5.045

Mike Herring 4.686

Jeff Wilkerson 4.902

 

It's just showing their q1, q2, q3 times. So it's showing all the times not just the one needed for ordering, it's just clumping them in groups.

Link to comment
Share on other sites

That's not what I was execting it to do :/

 

how about this:

 

SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
USING (ON r.id = above.regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
USING (ON r.id = below.regid)
WHERE r.class = '$var'}

 

Again, this is supposed to return only contestant names, and time used for ranking. Nothing else at this moment.

Link to comment
Share on other sites

$query = mysql_query("SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
USING (ON r.id = above.regid)
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
USING (ON r.id = below.regid)
WHERE r.class = '$var'") or DIE(mysql_error());

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON r.id = above.regid) LEFT JOIN (SELECT regid, MAX(et) AS et FROM index_q WHE' at line 7

Link to comment
Share on other sites

Lol... too fast...

 

$query = mysql_query("SELECT
  r.firstname, r.lastname, IF(above.et IS NOT NULL, above.et, below.et)
FROM
  registrations as r
LEFT JOIN
  (SELECT regid, MIN(et) AS et FROM index_q WHERE et >= 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS above
ON r.id = above.regid
LEFT JOIN
  (SELECT regid, MAX(et) AS et FROM index_q WHERE et < 4.7 AND eventid = '{$_GET['event']}' GROUP BY regid) AS below
ON r.id = below.regid
WHERE r.class = '$var'") or DIE(mysql_error());

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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