jeudi 4 décembre 2014

How to get the only row with MAX values for multiple columns on a SELECT query?


For SQL Server 2014.


I'm trying to build a select query to get ID, Child1 and Child2, where Child2 is the maxiumum value for @Parent = 'A'.



Child2 = (Child1 * 2) or ((Child1 * 2) - 1)




ID | Parent | Child1 | Child2
----|----------|---------|---------
258 | A | 4 | 7
262 | A | 4 | 8
277 | C | 15 | 29
263 | C | 15 | 30
202 | A | 7 | 13
219 | A | 7 | 14
4 | A | 8 | 15
220 | A | 8 | 16
278 | B | 4 | 7
9 | B | 4 | 8


For example:

Results for parameter @Parent = 'A': 220, 8, 16

Results for parameter @Parent = 'B': 9, 4, 8

Results for parameter @Parent = 'C': 263, 15, 30


I'm using this:



select ID, max(Child1) as CH1, max(Child2) as CH2 from Table1 where Parent = A group by ID


but the result is multiple:



ID | CH1 | CH2
----|---------|---------
258 | 4 | 7
262 | 4 | 8
202 | 7 | 13
219 | 7 | 14
4 | 8 | 15
220 | 8 | 16


How to get only the row with max values for Child1 and Child2?





Aucun commentaire:

Enregistrer un commentaire