SQL syntax need to modified on MySQL server with sql_mode=only_full_group_by

Question

Please, help me to make this my SQL syntax correct:

SELECT DISTINCT 
  kodemk, a.namamk, a.sks, IF( b.status='K', 0, a.smt ) as smt, 
  a.kompetensi, MIN(b.nilai) nilai, b.kodesmt
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, ( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, kodesmt, status 
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk, kodesmt, status) AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk

It gives me a result like this:

kodemk    namamk                           sks  smt  kompetensi  nilai  kodesmt
-----------------------------------------------------------------------------------
13000001  PENDIDIKAN AGAMA ISLAM           2    1    000         A      20141
13000002  PENDIDIKAN AGAMA KRISTEN         2    1    000         NULL   NULL
13000003  PENDIDIKAN AGAMA KATOLIK         2    1    000         NULL   NULL
13000004  PENDIDIKAN AGAMA HINDU           2    1    000         NULL   NULL 
13000005  PENDIDIKAN AGAMA BUDHA           2    1    000         NULL   NULL
13000006  PANCASILA                        2    1    000         B      20141
13000007  BAHASA INDONESIA                 2    1    000         B      20141
13500001  PENGANTAR TEKNOLOGI INFORMASI    2    1    500         A      20141
13500002  ALGORITMA DAN PEMROGRAMAN I      2    1    500         C      20141
13500003  PRAK. ALGORITMA & PEMROGRAMAN I  1    1    500         B      20141 
13500004  PERANGKAT LUNAK APLIKASI         2    1    500         B      20141
13500005  PRAK. PERANGKAT LUNAK APLIKASI   1    1    500         B      20141
13500006  STATISTIKA                       3    1    500         B      20141
13520001  KALKULUS I                       2    1    520         C      20151
13520001  KALKULUS I                       2    1    520         D      20141
13520002  FISIKA                           2    1    520         C      20141
13000008  PENDIDIKAN KEWARGANEGARAAN       2    2    000         B      20142
13000009  BAHASA INGGRIS                   2    2    000         B      20142
13500007  ALJABAR LINEAR                   3    2    500         B      20172
13500007  ALJABAR LINEAR                   3    2    500         E      20152 
13500007  ALJABAR LINEAR                   3    2    500         E      20162
13500007  ALJABAR LINEAR                   3    2    500         E      20142
13500007  ALJABAR LINEAR                   3    2    500         P      20161
13500008  ALGORITMA DAN PEMROGRAMAN II     2    2    500         B      20142
13500009  PRAK. ALGORITMA & PEMROGRAMAN II 1    2    500         C      20142
...
...

but I need only one record for every kodemk with best grade (nilai), like ALJABAR LINIER with B grade, and KALKULUS I with C grade

The problem occurs after I migrated my server with different version of MySQL servers. On previous version, I only using this query, and it works as I want:

SELECT DISTINCT 
  kodemk, a.namamk, a.sks, IF( b.status='K', 0, a.smt ) as smt, 
  a.kompetensi, MIN(b.nilai) nilai, b.kodesmt
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, ( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, kodesmt, status 
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk

but after migration I've got this error message:

Error Number: 1055

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sia40.kuliah.kodesmt' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

so, I modify BROUP BY clause by adding columns name

...
GROUP BY kodemk, kodesmt, status
...

but the result not suitable

I think, change sql_mode is not a good option

Edit:

Here is my table structure:

-- Table structure for table `matakuliah`

CREATE TABLE `matakuliah` (
  `prodi` varchar(5) DEFAULT NULL,
  `kodemk` varchar(10) DEFAULT NULL,
  `namamk` varchar(128) DEFAULT NULL,
  `sks` tinyint(4) DEFAULT NULL,
  `skst` tinyint(4) DEFAULT NULL,
  `sksp` tinyint(4) DEFAULT NULL,
  `sksl` tinyint(4) DEFAULT NULL,
  `smt` tinyint(4) DEFAULT NULL,
  `kelompok` varchar(5) DEFAULT NULL,
  `kompetensi` char(3) DEFAULT NULL,
  `tahunkur` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Sample data for table `siska_matakuliah`
--

INSERT INTO `matakuliah` (`prodi`, `kodemk`, `namamk`, `sks`, `skst`, `sksp`, `sksl`, `smt`, `kelompok`, `kompetensi`, `tahunkur`) VALUES
('57601', '112001', 'PRAKTEK KERJA & LAPORAN', 2, 0, 0, 2, 2, '', '111', '2003'),
('57601', '112102', 'TUGAS AKHIR (APLIKASI)', 2, 0, 0, 2, 2, '', '111', '2003'),
('57601', '111001', 'SISTEM OPERASI', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '111101', 'INTRANET & INTERNET', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '111401', 'PENGELOLAAN INSTALASI KOMPUTER', 2, 1, 1, 0, 1, '', '111', '2003'),
('57601', '110001', 'PENDIDIKAN AGAMA ISLAM', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110002', 'PENDIDIKAN AGAMA KRISTEN PROTESTAN', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110003', 'PENDIDIKAN AGAMA KRISTEN KATOLIK', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110004', 'PENDIDIKAN AGAMA HINDU', 2, 2, 0, 0, 1, '', '111', '2003'),
('57601', '110005', 'PENDIDIKAN AGAMA BUDDHA', 2, 2, 0, 0, 1, '', '111', '2003');


-- Table structure for table `kuliah`

CREATE TABLE `kuliah` (
  `kodesmt` varchar(5) DEFAULT NULL,
  `prodi` varchar(5) DEFAULT NULL,
  `nimhs` varchar(8) DEFAULT NULL,
  `kodemk` varchar(10) DEFAULT NULL,
  `nilai` char(1) DEFAULT NULL,
  `presensi` varchar(35) NOT NULL,
  `paralel` char(2) DEFAULT NULL,
  `kodekelas` varchar(9) DEFAULT NULL,
  `status` char(1) NOT NULL,
  `id_krs` char(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

Thank You, @TheImpaler

Based on your answer, I just understand about only_full_group_by mode, and based on this reference: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, I have noticed that ANY_VALUE() is also an agregate function that can be use if the fields not in GROUP BY list

So, I just modify my very bad query, and now it works as I need

SELECT 
  a.kodemk, a.namamk, a.sks, IF( b.status='K', 0, a.smt ) smt, 
  a.kompetensi, b.nilai, b.kodesmt 
FROM 
  (SELECT * FROM matakuliah 
    WHERE tahunkur= '2013' 
    AND (prodi= '55201' OR kompetensi= '523')) a 
  LEFT JOIN 
  (SELECT 
      kodemk, ( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) nilai, 
      ANY_VALUE(kodesmt) kodesmt,   -- modified, added ANY_VALUE()
      ANY_VALUE(status) status      -- modified, added ANY_VALUE() 
    FROM kuliah 
    WHERE nimhs='52014024' AND status<>'B' 
    GROUP BY kodemk) b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, akodemk

Thank's also for all comments and suggestion

Answer 2

All columns not mentioned in the GROUP BY clause must be aggregated. Older versions of MySQL didn't check this and the result was... well... "random" to say the least, "undefined" in the worst case.

Any aggregation function will work. For example, you can use MIN(), MAX(), SUM(), AVG(), etc. In your particular query I would add them, as in:

SELECT DISTINCT 
  IF( b.status='K', 0, a.smt ) as smt, -- in the GROUP BY; no change needed
  a.kompetensi, -- in the GROUP BY; no change needed
  kodemk, -- in the GROUP BY; no change needed
  min(a.namamk), -- added MIN() here; change function as needed
  min(a.sks, -- added MIN() here; change function as needed
  MIN(b.nilai) nilai, -- was already aggregated in your query
  min(b.kodesmt) -- added MIN() here; change function as needed
FROM 
  (SELECT * 
   FROM matakuliah 
   WHERE tahunkur='2013' AND (prodi= '55201' OR kompetensi= '523')) AS a 
  LEFT JOIN 
  (SELECT 
     kodemk, -- in the GROUP BY; no change needed
     min( IF( MIN(nilai)<>'', MIN(nilai),'P' ) ) AS nilai, -- added MIN()
     min(kodesmt),  -- added MIN()
     min(status) -- added MIN()
     FROM kuliah 
     WHERE nimhs= '52014024' AND status<>'B' 
     GROUP BY kodemk AS b 
  USING(kodemk) 
ORDER BY smt, a.kompetensi, kodemk