How to SELECT multiple columns during a MAX use

Question

I'm attempting to write a query that gets the greatest amount a person has paid and the date that payment was processed for everyone in my database. My problem is ever time the query runs I get a different date as it's not correlating Max(pmt_amout) to the date of the record. My goal is to be able to run the query for all persons. Here's what I have so far.

select person_id, 
MAX(pmt_amount) as greatest_payment, 
pmt_date as greatest_payment_date
from payments p 
group by person_id

Thank you so much for you help!

This is the most frequently asked question under this tag, and it even has its own chapter in the manual. Nevertheless, if you're still struggling see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 02 '18 at 08:46
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Feb 02 '18 at 09:05
Read the manual re group by, you are not using it properly. Google my comments re googling many versions of your question. — philipxy, Feb 02 '18 at 09:05

score 0 · Answer 1 · answered Feb 02 '18 at 08:55

You can try this solution for your problem:

Table Structure actual Implemented :

Table :

CREATE TABLE payments ( 
id MEDIUMINT NOT NULL AUTO_INCREMENT,
person_id int(25),
pmt_amount int(25),
pmt_date date,
PRIMARY KEY (id) ) #ENGINE=MyISAM;

Insert statement :

INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(1,50500,'2017-05-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(1,50500,'2018-05-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(1,10200,'2018-05-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(2,80200,'2018-05-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(3,9300,'2018-06-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(3, 15000,'2018-04-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(4,18000,'2018-03-12');
  INSERT INTO payments (person_id,pmt_amount,pmt_date)
  VALUES(4,15000,'2018-06-12');

Query :

select person_id, 
MAX(pmt_amount) as greatest_payment, 
pmt_date as greatest_payment_date
from payments p 
group by person_id
ORDER BY pmt_date DESC

Ans also visit SQLfiddle for your problem

I Hope it will help.

score 0 · Answer 2 · answered Feb 02 '18 at 09:31

Your problem is wrong usage of group statement, when selected column is not part of group by expression ANY value is selected. As mention by @philipxy please check Error related to only_full_group_by when executing a query in MySql

Try:

select p.* 
from payments as p INNER JOIN
(select p2.person_id, MAX(p2.pmt_amount) max_amount from payments p2 group by p2.person_id) sub
on p.person_id = sub.person_id and p.pmt_amount = sub.max_amount

How to SELECT multiple columns during a MAX use

2 Answers2