SQL for selecting single record from duplicates based on a condition-CodePudding

I am trying to write a SQL, which picks up one record among the duplicates (if exist), based on highest production volume. A sample input and output:

If there are duplicate KEYS, in the above example-A-2, it should check the volume, and pickup the record having higher volume(i.e. one with 42 volume in the above example)

Can anyone help me with the code?

CodePudding user response：

try like below using corelated sub query

select t1.* from input t1
where t1.volume=( select max(volume) from input t2 where t1.Key=t2.key)

CodePudding user response：

Try using Subquery

Select * from Table1 a where Volume=(Select MAX(Volume) from Table1 where [Key]=a.[Key]) order by [Key]