How to select the record contains MAX(some_field) within GROUP(group by)

Question

SELECT MAX(some_field) FROM table_A GROUP BY another_field.

this only get the max value of the 'some_field', I wanna get the whole record which contains the MAX(some_field).

thanks.

Answer 1

select a.*
from table_A a
inner join (
    SELECT another_field, MAX(some_field) as MaxSomeField
    FROM table_A 
    GROUP BY another_field
) am on a.another_field = am.another_field and a.some_field = am.MaxSomeField

Answer 2

SELECT
  *
FROM
  table_A
  INNER JOIN (
      SELECT MAX(some_field) AS some_field, another_field
        FROM table_A
    GROUP BY another_field
  ) AS max ON table_A.some_field    = max.some_field
          AND table_A.another_field = max.another_field

Note that you will get multiple rows of another_field if MAX(some_field) is not a unique value in that group. You'd have to group the above again to get rid of those "duplicates".

Answer 3

Select * From table_A withMax Where Not Exists
    (Select * From table_A Where some_field > withMax.some_field)

Usually, you'll be doing that with some other criteria, and you'll want to check for duplicates, so a realistic example is more like this:

Select * From table_A withMax Where account_id = 1234 Not Exists
    (
    Select * 
    From table_A 
    Where account_id = withMax.account_id And 
        (
        some_field > withMax.some_field
        Or (some_field = withMax.some_field And id > withMax.id)
        )
    )