Finding lowest values in database

robp,

If you're using ADO you can simply use the MaxRecords property & ORDER BY command:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=YourDSN;UID=YourID;PWD=YourPW"
Set RSLowScores = Server.CreateObject("ADODB.Recordset")
RSLowScores.MaxRecords = 10
SQL = "SELECT * FROM YourTable "
SQL = SQL & "ORDER BY score ASC"
RSLowScores.Open SQL,Conn,1,2
%>

That will just return the 10 lowest scores. If you're not using ADO you can still achieve the same results with a bit more work:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=YourDSN;UID=YourID;PWD=YourPW"

SQL = "SELECT * FROM YourTable"
SQL = SQL & "ORDER BY score ASC"

Set GetLowestScores = Conn.Execute(SQL)

' Do a loop until we reach 10
Dim MaximumRecord
MaximumRecord = 0
Do while MaximumRecord <> 10
%>
Your db results go here
<% MaximumRecord = MaximumRecord + 1
GetLowestScore.movenext
loop %>

<% 'Loop Done
Set GetLowestScore = nothing %>

That should do the trick, you would obviously have to add error checking.

Forgot to mention this example is ASP & SQL 7.

Regards,
Peter J. Boettcher

[This message has been edited by Peter J. Boettcher (edited 30 April 2000).]

Thanks a lot.

I have it working now using:
$num = 10;
$query="SELECT * FROM $table ORDER BY SCORE ASC LIMIT $num";
$mysql_result = mysql_query($query);

------------------
Personal Site
http://www.wiredstart.com : The Technology Start Page

Actually, this isn't exactly correct. The reason is that it does not take ties into account; the 10th entry may have the exact same value as the 11th entry in this case, and you'd never know it.

A different way to do this is with the following SQL (courtesy of Joe Celko's SQL for Smarties book):

select distinct count(*) , a.salary
from employees A, employees B
where a.salary <= b.salary
group by a.salary
having count(*) <= 10

The problem with this SQL is that since it is essentially cartesian-joining two tables together, it doesn't work too well with huge tables. The concept is that each row in your table will be joined to every row in the table that has a salary less than or equal to the first row. Your #1 salary will be joined to itself (having a count(*) of 1), the #2 salary will be joined to the #1 salary and itself (having a count(*) of 2), etc. The HAVING clause limits your rows to the top 10.

The best part of this is that it doesn't use sub-selects (mySQL can't handle them) but it can be written with sub-selects also. It doesn't perform any better, but is a bit more clear:

SELECT DISTINCT salary
FROM Personnel AS P1
WHERE 10 >= (SELECT COUNT(*)
FROM Personnel AS P2
WHERE P1.salary < P2.salary)

Ralph
http://www.hockeydb.com