Have Geek Brain

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

SELECT MAX(some_field) FROM table_A GROUP BY another_field

It only gets the maximum value of "some_field"; I want to get the entire record containing MAX(some_field)

.

+2


a source to share


3 answers 


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


+6


a source 


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 another_field

if MAX(some_field)

not unique in this group. You will have to group above to get rid of these "duplicates".

+4


a source 


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

Typically you will do this with some other criteria and you will need to check for duplicates, so a realistic example looks 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)
        )
    )
+1


a source






More articles:


All Articles