CODEWITHSUNDEEP
mysqlsqlgroup-by
ivg adil
ivg adil

Reputation: 3

GROUP and ORDER Mysql get diffrent data in column

this is all data from current table

SELECT
  id,harga,kode_tahun_ajaran
FROM
  tblharga
+----+---------+-------------------+
| id | harga   | kode_tahun_ajaran |
+----+---------+-------------------+
|  1 |  400000 | THN2018/2019      |
|  2 |   50000 | THN2018/2019      |
|  3 | 1000000 | THN2018/2019      |
|  4 |  900000 | THN2018/2019      |
|  5 |  500000 | THN2017/2018      |
|  6 |  600000 | THN2018/2019      |
+----+---------+-------------------+

and i run this code to get the last harga with grouping the kode_tahun_ajaran

  SELECT
    id,harga,kode_tahun_ajaran
  FROM
    tblharga
  GROUP BY
    kode_tahun_ajaran
  ORDER BY id DESC
+----+--------+-------------------+
| id | harga  | kode_tahun_ajaran |
+----+--------+-------------------+
|  5 | 500000 | THN2017/2018      |
|  1 | 400000 | THN2018/2019      |
+----+--------+-------------------+

the harga column should take the last data which is 600000 because of the order by code.

how can I retrieve the latest data by grouping it another way? thanks in advance.

Upvotes: 0

Views: 57

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try below 

SELECT
    id,harga,kode_tahun_ajaran
  FROM
    tblharga where id in (select max(id) from tblharga group by kode_tahun_ajaran)

Upvotes: 1

Luca Lupidi
Luca Lupidi

Reputation: 164

select id,S.kode_tahun_ajaran,harga
from tblharga S inner join
( select kode_tahun_ajaran,max(id) maxid from tblharga group by kode_tahun_ajaran) T
where T.kode_tahun_ajaran=S.kode_tahun_ajaran and maxid=id;

Upvotes: 0

Related Questions