CODEWITHSUNDEEP
sqlsas
QuantumMechanics
QuantumMechanics

Reputation: 141

Keeping observations with maximal value of two columns

I've got a table like:

ID Value1 Value2 ...
1  4      80
1  5      99
1  5      100
2  7      30
2  7      33
2  22     11

Now, I would like to keep all observations where for each ID and Value1 the value of Value2 is the highest, i.e. the outcome shall look like:

ID Value1 Value2 ...
1  4      80
1  5      100
2  7      33
2  22     11

Does anyone got any ideas?

Best

Upvotes: 1

Views: 59

Answers (7)

Andrew Haynes
Andrew Haynes

Reputation: 2640

If using SAS and want to avoid SQL, you could sort all your variables in descending order and use a by statement with the first.ID statement to take only the first value ( which after sorting will be the highest).

proc sort data = have;
  by ID descending Value1 descending Value2 ...
run;

data want;
  set have;
  by ID;
  if first.ID;
run;

Note: Depending on the size of your data this may not be the most efficient due to sorting multiple columns.

Upvotes: 1

Longfish
Longfish

Reputation: 7602

Here's a SAS version. The id statement in proc summary will automatically return the maximum value. The class statement is equivalent to group by. The drop statement gets rid of the automatic variables (_type_,_freq_) create in the procedure.

data have;
input ID Value1 Value2;
datalines;
1  4      80
1  5      99
1  5      100
2  7      30
2  7      33
2  22     11
;
run;

proc summary data=have nway;
class id value1;
id value2;
output out=want (drop=_:);
run;

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Using Group By clause to achieve your result : 

SELECT ID ,Value1 ,MAX(Value2)
FROM Your_Table 
GROUP BY ID ,Value1

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Try This.

        select ID ,value1,Value2 from
        ( select ID ,value1,Value2,row_number() over
         ( partition by ID,Value1 order by Value2 desc )as rnk
         from 
        #TableName
        )a where rnk =1

OR 

             select ID ,Value1,max(Value2)from 
            #TableName
            group by ID ,Value1

Upvotes: 0

littlefeltfangs
littlefeltfangs

Reputation: 454

Using group by and joining on a sub select gets you 

SELECT *
FROM the_table t1
JOIN (
    SELECT id, value1, MAX(value2) AS value2
    FROM the_table    
    GROUP BY ID ,value1
) t2 ON t1.id = t2.id
  AND t1.value1 = t2.value1
  AND t1.value2 = t2.value2

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Just use Group By with Max function

SELECT ID,  VALUE1 , MAX(VALUE2) AS VALUE2
FROM TABLE1
GROUP BY ID, VALUE1

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

Assuming ROW_NUMBER is supported

select      *

from       (select      t.*
                       ,row_number() over 
                            (partition by ID,Value1 order by Value2 desc) as rn

            from        my_table 
            ) t

where       rn = 1

Upvotes: 0

Related Questions