SQL Server : select rows with maximum value

Question

SQL Server syntax - I have this table:

C1 - C2 - C3
--------------
 2 - AA - 10
 2 - AA - 20
 3 - AA - 15
 3 - AA - 16
 3 - AA - 56
 1 - BB - 54
 2 - BB - 53
 2 - BB - 89

I need a query that will return this result:

C1 - C2 - C3
--------------
 3 - AA - 15
 3 - AA - 16
 3 - AA - 56
 2 - BB - 53
 2 - BB - 89

So it should return only rows with maximum value of C1 over C2.

How can I do that ?

Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Ale, Mar 30 '18 at 09:09

Jay Shankar Gupta · Accepted Answer · 2018-03-30T09:14:39.593

1

SELECT t1.C1,t1.C2,t1.C3
FROM Table1 t1 INNER JOIN (SELECT MAX(C1) C1,C2 FROM table1 GROUP BY C2)
as t2 on t1.C1=t2.C1 and t1.C2=t2.C2
Order by C2

Output

Live Demo

http://sqlfiddle.com/#!18/3b54f/13

edited Mar 30 '18 at 09:14

answered Mar 30 '18 at 09:09

Jay Shankar Gupta

score 1 · Answer 2 · answered Mar 30 '18 at 09:10

1

You could use a inner join on max c1 for c2

  select m.* 
  from my_table  m
  inner JOIN  ( 
  select max(C1) max_c1, C2
  from my_table 
  group C2 ) t on t.max_c1 = m.C1 and t.C2 = m.C2

answered Mar 30 '18 at 09:10

ScaisEdge

2 Answers2