How to get the last record in a set of records?

[benphp]

This should be easier - I have a list of records:

 

ID FRUIT

1 Apple

2 Apple

3 Apple

4 Pear

5 Pear

6 Banana

7 Banana

 

I want to get:

 

3 Apple

5 Pear

7 Banana

 

How do you do this with SQL? MUST I use a Select statement within a Select statement - or is there a dead simple way?

 

[Push Eject]

SELECT * FROM table ORDER BY yourchoice DESC LIMIT 1

[dropfaith]

wouldnt that limit to one result overall  and only get Id 7

[benphp]

Yes, that limits the result to one record.

 

I think I need to use MAX somehow, but I can't get it to work. Here's the actual code that I've been trying with no success:

 

<?php
include('common/functions.php');
$dbConn = fnOpenDbConn();

print "<table>";
$selectR = "
SELECT DISTINCT rid, score, uid, MAX(moddate) as Moddate 
FROM resp 
WHERE uid = '204'
GROUP BY resp.rid, resp.score, resp.moddate 
ORDER BY resp.rid
";

$resultR = mysql_query($selectR) or trigger_error("SQL", E_USER_ERROR);
$numrowsR = mysql_num_rows($resultR);
print "$numrowsR";
while($rowR = mysql_fetch_row($resultR)) {
$rid	= $rowR[0];
$score	= $rowR[1];
$uid	= $rowR[2];
$moddate	= $rowR[3];
print "<tr><td>$rid</td><td>$score</td><td>$moddate</td><td>$uid</td></tr>";
}
print "</table>";

mysql_close($dbConn);
?>

[Push Eject]

I'm sorry, your subject is how to the last record in a set of records...

 

I should have read your post thoroughly.

[benphp]

I did it the hard way:

 

<?php
include('common/functions.php');
$dbConn = fnOpenDbConn();

print "<table>";
$selectR = "
select rid, score, uid, moddate, rcount
from resp r1
where rcount = (
    select rcount
    from resp r2
    where r2.uid=r1.uid
ORDER BY r2.rcount DESC LIMIT 1
    )
AND uid = '204'
";
//print $selectR;
$resultR = mysql_query($selectR) or trigger_error("SQL", E_USER_ERROR);
$numrowsR = mysql_num_rows($resultR);
while($rowR = mysql_fetch_row($resultR)) {
$rid	= $rowR[0];
$score	= $rowR[1];
$uid	= $rowR[2];
$moddate	= $rowR[3];
$rcount	= $rowR[4];
print "<tr><td>$rid</td><td>$score</td><td>$moddate</td><td>$uid</td><td>$rcount</td></tr>";
}
print "</table>";

mysql_close($dbConn);
?>

 

For anyone who comes along later and wants to see how it's done - here's the table:

 

+------+------+------+------+-------+---------+-------+--------+------------+
| rid  | pid  | qid  | uid  | score | ractive | shift | rcount | moddate    |
+------+------+------+------+-------+---------+-------+--------+------------+
|  479 |    1 |  162 |  204 | 1     | NULL    | 1     | 1      | 2008.09.22 |
|  480 |    1 |  163 |  204 | 1     | NULL    | 1     | 1      | 2008.09.22 |
|  481 |    1 |    1 |  204 | 1     | NULL    | 1     | 1      | 2008.09.22 |
|  482 |    1 |    2 |  204 | 3     | NULL    | 1     | 2      | 2008.09.23 |
|  483 |    1 |    3 |  204 | 3     | NULL    | 1     | 2      | 2008.09.23 |
|  484 |    1 |    4 |  204 | 3     | NULL    | 1     | 3      | 2008.09.24 |
|  485 |    1 |   31 |  204 | 3     | NULL    | 1     | 4      | 2008.09.25 |

[fenway]

Yes, that is the hard way... usually, you get the most recent for each one, then join that table back.