newbie sql statement help

[Bob Ramsey]

The people who run our mysql server are using version 3.23.51, so I don't have access to some of version 4's features like sub selectes.

I have a table with data like this:

firm firm_rank time time_rank student
A 1 10 1 jones
A 1 10 1 smith
A 1 10 1 alvin
A 2 10 1 bob
A 2 10 2 charlie
B 2 10 1 jones
B 2 10 1 smith

and so forth. Each student can rank a firm 1-10 and an interview time 1-10. There are usually about 30 firms and about 200 time slots.

My goal is to pick a random student from the highest firm rank and then from the highest time rank for each time. So someone who ranks the firm as 2 and the time as 1 loses to someone who ranks the firm as 1 and the time as 3. If Jones is picked for Firm A at 10, she is ineligible for any more Firm A slots and all 10 o'clock slots regardless of firm.

I can almost see a way to use the unique, random, and order by features to make one really complex select statement that just pulls everything out, but I can't do it. If it can't be done, I'm willing to loop through doing individual firms and just selecting out a random high ranking person for each time, but I can't see that either.

I'm using mysql and php. Any ideas? I'd hate to just keep looping through and making lot's of small select statements, which I know I can do. It seems like there's a better way.

Thanks,

bob




======================================================================
Bob Ramsey Applications Development & Support II
ph: 1(319)335-9956 216 Boyd Law Building
fax: 1(319)335-9019 University of Iowa College of Law
mailto:[EMAIL PROTECTED] Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
======================================================================


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php