我在查询中需要Sql帮助

Question

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

Answer 1

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)