Remove duplicates from cfquery based on a particular column

Question

I have got two cfquery result with same column name. I want to merge them in such a way that I remove the duplicates based on a particular colum.

So it should be like merge query 1 with query 2 where value of query1.column1 > query2.column1

Is there a way to get this done by MS SQL?

My resulteset is like this at the moment

|name |value| temp_id
test1   1     001
test1   2         001
test2   2         002
test2   1         002

I want to get the rows with distinct column (name) and higest value

So from above example I want

test1 with value 2 and test 2 with value 2

Answer 1

If you're only looking to get the name and the maximum value then you can just do

SELECT name, MAX(value) AS value
FROM yourtable
GROUP BY name