How to show last data (max data) of each group by on mysql

Question

I have query like below:

SELECT kd.id_karir, kd.nama, kd.kelamin, 
        (YEAR(NOW())-YEAR(tanggal)) usia, MAX(pf.jenis), pf.jenis, 
        pf.nama AS pendidikan, pf.jurusan, kd.alamat, kd.telepon, 
        kd.handphone, kd.email, kd.tempat AS tempat_lahir, 
        kd.tanggal AS tanggal_lahir 
FROM keadaan_diri AS kd 
    LEFT OUTER JOIN pendidikan_formal AS pf ON (kd.id_karir = pf.id_karir) 
WHERE kd.id_karir = 'P1409047' 
GROUP BY kd.id_karir 
ORDER BY kd.nama ASC, pf.jenis DESC

I mean to returning the last data on the table pendidikan_formal using max and group but the query doesn't work.

You are welcome ! Please provide sample table data or expected output.. — Ecd, Oct 05 '18 at 09:38
Cobain baca [post ini](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) mas/mba.. — Ascaliko, Oct 05 '18 at 10:04
@user2745025 Welcome to StackOverflow. Please accept an answer if it helped or add more details to the question. — steffen, Oct 06 '18 at 18:48

steffen · Answer 1 · 2018-10-05T10:14:26.703

First of all, you can / should (depending on the MySQL configuration) only select and order by columns that are part of your group by clause. For all other columns, you have to specify an aggregation function. For example, let's say you have two records of humans, both have the same name and a different age. When you group by name, you have to choose one of the two age values (max, min, average, ...). If you don't care which, you could turn off sql mode only full group by. I wouldn't suggest that however.

In order to get the one record with some maximum value however, group by is not the right approach. Take a look at these examples:

Subselect:

SELECT name, age, ...
FROM   humans
WHERE  age=(SELECT MAX(age) FROM humans);

Order by and limit:

SELECT name, age, ...
FROM humans
ORDER BY age DESC
LIMIT 1;

Left join:

SELECT name, age, ...
FROM humans h1
LEFT JOIN humans h2 ON h1.age < h2.age
WHERE h2.age IS NULL;

Now if you want all maximum rows per group, check one of these answers with tag greatest-n-per-group.

@user2745025 If something helped or answered your question, please upvote or accept answers. — steffen, Oct 08 '18 at 06:33

score 0 · Answer 2 · answered Oct 05 '18 at 11:15

You can use a correlated subquery. Your question is a bit vague; I assume that id_karir is the group and tanggal is the date.

If I understand correctly, this would apply to your query as:

SELECT kd.id_karir, kd.nama, kd.kelamin, 
        (YEAR(NOW())-YEAR(tanggal)) usia, pf.jenis, pf.jenis, 
        pf.nama AS pendidikan, pf.jurusan, kd.alamat, kd.telepon, 
        kd.handphone, kd.email, kd.tempat AS tempat_lahir, 
        kd.tanggal AS tanggal_lahir 
FROM keadaan_diri kd LEFT OUTER JOIN
     pendidikan_formal pf
     ON kd.id_karir = pf.id_karir AND
        pf.tanggal = (SELECT MAX(pf2.tanggal) FROM pendidikan_formal pf2 WHERE pf2.id_karir = pf.id_karir)

This is not an aggregation query. This is a filtering query.

How to show last data (max data) of each group by on mysql

2 Answers2