I have a table that contains 2 keys (iId and iVersion) and some other data:

iId int(4) NOT NULL default '0',
iVersion int(4) NOT NULL default '0',
vchName varchar(50) default NULL

The data looks a little like this:

1|1|Fred
1|2|Fred edited once
1|3|Fred edited twice
2|1|Dave
2|2|Dave edited once
2|3|Dave edited twice

I need a sql statement that will return all columns of the latest row (based on the greatest iVersion value) for each unique iId in the table.

So using the data above it should return

1|3|Fred edited twice
2|3|Dave edited twice

Any help would be appreciated!

select iId,
iVersion,
vchName
from <table_name>
group by iId
having iVersion = max(iVersion)

Try:
Or if using Oracle you could use the analytic functions.

I would suggest using:-PatP

May have been that i was using MySQL but none of the above worked!

They did however help me to get some sql that does work....

select iId, vchName, MAX(iVersion) as iVersion
from <Table>
GROUP BY iId

Thanks.

you may think this works, but it doesn't

even the mysql docs tell you that this gives unpredictable results (holler if you need the link to the page in the docs where it explains this)

what you want is the vchName that comes from the row which has the largest iVersion, but this is not what you are getting, and if it looks like you are getting it, it is a fluke

you could just as easily get this instead --

1|3|Fred edited once
2|3|Dave

i'm sorry if this sounds like i'm dumping all over you, it's not your fault, it's mysql's fault for allowing non-standard sql to run (in any other database system, your query would generate a syntax error)

here's what you want, done without subqueries if you're not on 4.1 yet --