Hi again, Larry!
This should do the trick if you're using 4.1+:
SELECT a.id, a.stockcode, a.runningbalance
FROM TableA a
INNER JOIN (
SELECT MAX(id) as id
FROM TableA
GROUP BY stockcode
) AS b
ON a.id = b.id;
if not, do:
CREATE TEMPORARY TABLE max_ids
SELECT MAX(id) as id
FROM TableA
GROUP BY stockcode;
SELECT a.id, a.stockcode, a.runningbalance
FROM TableA a
INNER JOIN max_ids b
ON a.id = b.id;
or, alternately, if your running balances only ever go *up*, then you can simply do:
SELECT MAX(id) as id, stockcode, MAX(runningbalance) as runningbalance
FROM TableA
GROUP BY stockcode;
this won't work if your running balances can go up *and* down, however, as you cannot rely on the last running balance to be the greatest running balance over time...
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com