what query would I use to do the following
Name1 Version1 xAmount
Name1 Version2 xAmount
Name2 Version1 xAmount
Name2 Version2 xAmount
Name2 Version3 xAmount
Name3 Version1 xAmount
according to this The latest version for Name1 is Version2, and the latest version for Name2 is Version3, and Name3 is Version1.
How would i create a query to grab each name with the latest version, assuming the latest version is an integer or something like 1.2a and 1.2b
use the following
select Name,Version, Amount
From T1,(select Name,max(Version) as Version From T1 Group By Name) as T2
Where T1.Name=T2.Name and T1.Version=T2.Version
and if you have an ID for the table
select Name,Version, Amount
From T1,(select max(ID) as ID From T1 Group By Name) as T2
Where T1.ID=T2.ID
Or
select Name,Version, Amount
From T1
Where T1.ID IN (select max(ID) as ID From T1 Group By Name)
First of all, I strongly recommend you to keep a numeric column indicating wich iteration of the version it is, even if you have a column with the name. So, assuming you do that, then the solution for what you want its very simple:
SELECT Name, MAX(version) as MaxVersion
FROM Table
GROUP BY Name