Neverside

A SELECT statement question...

Ok...I am going to describe my problem, and hopefully I can get some help.

I am creating a golf handicapping system. Below is my table setup:

Table - scores

Code:

id | userid | courseid | teeid | dateplayed | notes | diff | scorenum

Table - courses

Code:

id | name | address | city | st | zip | country | enteredby

Table - tees

Code:

id | courseid | enteredby | tees

I have cut off some of the fields as they aren't necessary for this.

Anyway, what I am trying to do is select the last twenty scores entered into the scores table by a particular user. To do this, I would think I would use this SELECT statement:

Code:

SELECT * FROM scores WHERE userid=x LIMIT 20

Now, from those last 20 scores, I need to select the 10 best. How would I do that???
::classic:

Thanks, and let me know if I can explain anything any further than what is above...

underparnv

SELECT * FROM `scores` WHERE `userid` = 'x' ORDER BY `score` ASC LIMIT 10

That will get you the best 10 from a user sorted from lowest to highest.

___________________

angelessme, antagonising neverside members, staff and administration since 2001.

Quote:

SELECT * FROM `scores` WHERE `userid` = 'x' ORDER BY `score` ASC LIMIT 10

That will get you the best 10 from a user sorted from lowest to highest.

I know of the limit cause, however, I am trying to first sort it by date, thus obtaining the 20 most recent scores, then I need to sort it by differential in ascending order. Then from that sort, I will need to grab the first 10...

Does that make sense?

You need to run mysql subqueries/routines or something, so you can run a query on the result of another query, i _think_ (you'll have to look it up) this is implemented in the mysqli module for php5, you will really have to look it up.

___________________

angelessme, antagonising neverside members, staff and administration since 2001.

I'm on a free host, and they have php 4.something.

Any ideas from there?

use pgsql?

___________________

angelessme, antagonising neverside members, staff and administration since 2001.

Quote:

Originally posted by angelessme
use pgsql?

What is pgsql?

You don't _have_ to use mySQL for everything. You can select the first 20 and then from that just do array_multisort for the highest scores (sort by highest scores, then sort those by the date they scored.. ie if 2 people have a score of 10, but one scored it sooner than the other, it would put that person first, then the other second), and then do a loop to echo the highest 10.

___________________

Travis Farrell

Last edited by Motorspin, August 11th, 2004 03:06 AM (Edited 1 times)

I believe you can order by 2 things in MySQL... like:
ORDER BY date, score;

Not 100% sure because i haven't tried this in MySQL (but read about it in an SQL guide a long time ago). Anyway, it should work. Also, sub-queries only work on MySQL 4, just so you know (the version that's no longer free for servers).

As Motorspin said, probably the best way is to use PHP for the score calculation/display, not SQL.

___________________

Learn HTML