CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
bvnbhati
bvnbhati

Reputation: 380

MySQL max value in row

I am facing a problem with MySQL query which is a variant of "Id for row with max value". I am either getting error or incorrect result for all my trials.

Here is the table structure

Row_id
Group_id
Grp_col1
Grp_col2
Field_for_aggregate_func
Another_field_for_row

For all rows with a particular group_id, I want to group by fields Grp_col1, Grp_col2 then get max value of Field_for_aggregate_func and then corresponding value of Another_field_for_row.

Query I have tried is like below

SELECT c.*
FROM mytable as c left outer join mytable as c1
on (
    c.group_id=c1.group_id and
    c.Grp_col1 = c1.Grp_col1 and
    c.Grp_col2 = c1.Grp_col2 and
    c.Field_for_aggregate_func > c1.Field_for_aggregate_func
)
where c.group_id=2

Among alternative solutions for this problem I want a high performance solution as this will be used for large set of data.

EDIT: Here is the sample set of row and expected answer

Group_ID Grp_col1 Grp_col2  Field_for_aggregate_func Another_field_for_row  
2           --      N       12/31/2015               35 
2           --      N       1/31/2016                15 select 15 from group for max value 1/31/2016

2           --      Y       12/31/2015               5  
2           --      Y       1/1/2016                 15 
2           --      Y       1/2/2016                 25 
2           --      Y       1/3/2016                 30 select 30 from group for max value 1/3/2016

Upvotes: 0

Views: 118

Answers (2)

wwkudu
wwkudu

Reputation: 2796

You can use a sub-query to find the maximums, then join that with the original table, along the lines of:

select m1.group_id, m1.grp_col1, m1.grp_col2, m1.another_field_for_row, max_value
from mytable m1, (
  select group_id, grp_col1, grp_col2, max(field_for_aggregate_func) as max_value
  from mytable
  group by group_id, grp_col1, grp_col2) as m2
where m1.group_id=m2.group_id
  and m1.grp_col1=m2.grp_col1
  and m1.grp_col2=m2.grp_col2
  and m1.field_for_aggregate_func=m2.max_value;

Watch out for when there is more than one max_value for the given grouping. You'll get multiple rows for that grouping. Fiddle here.

Upvotes: 1

Utsav
Utsav

Reputation: 8143

Try this.

See Fiddle demo here

http://sqlfiddle.com/#!9/9a3c26/8 

Select t1.* from table1 t1 inner join
(
Select a.group_id,a.grp_col2,
A.Field_for_aggregate_func, 
count(*) as rnum from table1 a
Inner join table1 b
On a.group_id=b.group_id
And a.grp_col2=b.grp_col2
And a.Field_for_aggregate_func
<=b.Field_for_aggregate_func
Group by a.group_id,
a.grp_col2,
a.Field_for_aggregate_func) t2
On t1.group_id=t2.group_id
And t1.grp_col2=t2.grp_col2
And t1.Field_for_aggregate_func
=t2.Field_for_aggregate_func
And t2.rnum=1

Here first I am assigning a rownumber in descending order based on date. The selecting all the records for that date.

Upvotes: 0

Related Questions