MySQL Forums
Forum List  »  InnoDB

Re: Retrieving query
Posted by: Jay Pipes
Date: July 04, 2005 11:50PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2886
July 04, 2005 09:56PM
Re: Retrieving query
1677
July 04, 2005 11:50PM
1905
July 05, 2005 12:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.