-1

I have 2 tables. The first table is called as simas_barang and the second table is called as simas_mutasi.

simas_barang

id jenis nama
1 ATK DAN SEJENISNYA KERTAS HVS
2 INVENTARIS PRINTER L-3150
3 ATK DAN SEJENISNYA VIXAL

simas_mutasi

kode_barang nama tanggal sisa
3 vixal 2022-06-02 10
1 KERTAS HVS 2022-06-02 10
3 vixal 2022-06-03 7
3 vixal 2022-06-09 9
3 vixal 2022-06-16 8

I want to get one nama from a new updated tanggal(date). So it's going to be like

nama tanggal sisa
KERTAS HVS 2022-06-02 10
vixal 2022-06-16 8

I tried to make a query but it didn't work

SELECT simas_barang.nama, max(simas_mutasi.tanggal), simas_mutasi.sisa
    FROM ws_fh.simas_mutasi 
    INNER JOIN ws_fh.simas_barang
    ON simas_barang.id=simas_mutasi.kode_barang
    where (simas_barang.jenis = 'ATK DAN SEJENISNYA')
    and (tanggal >= '2022-06-01') and (tanggal <= '2022-06-31')
    group by simas_barang.nama, tanggal
jarlh
  • 42,561
  • 8
  • 45
  • 63
fathimah
  • 31
  • 5
  • Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – Jonas Metzler Mar 09 '23 at 08:18
  • You typically `GROUP BY` the same columns as you `SELECT` - _except those who are arguments to set functions_. – jarlh Mar 09 '23 at 08:35
  • 2
    Are you using MySQL or MS SQL Server? (Remove the other dbms tag.) – jarlh Mar 09 '23 at 08:36
  • "I tried to make a query but it didn't work" - why does that mean? Had a syntax error? Returned the wrong results - if so what? – Dale K Mar 09 '23 at 08:44
  • Your query selects from two tables, but there's only sample data for one of them. Make it easy to assist you, include a complete [mcve]. – jarlh Mar 09 '23 at 08:53
  • I've gone ahead and removed the conflicting tags; you'll need to (re)add the appropriate tag(s) in an [edit]. – Thom A Mar 09 '23 at 08:58
  • @jarlh I am using MySQL. I am sorry for making you confused. – fathimah Mar 10 '23 at 01:00
  • You typically GROUP BY the same columns as you SELECT - except those who are arguments to set functions. I.e. try `GROUP BY simas_barang.nama, simas_mutasi.sisa` and see what happens. – jarlh Mar 10 '23 at 08:34
  • @jarlh it didn't catch one row of each nama from the updated date – fathimah Mar 11 '23 at 04:13

1 Answers1

0

Order by the date in descending order and take the fist one:

SELECT simas_barang.nama, simas_mutasi.tanggal, simas_mutasi.sisa
FROM ws_fh.simas_mutasi 
  INNER JOIN ws_fh.simas_barang ON simas_barang.id=simas_mutasi.kode_barang
WHERE simas_barang.jenis = 'ATK DAN SEJENISNYA'
AND tanggal between '2022-06-01' AND '2022-06-31'
ORDER BY simas_mutasi.tanggal DESC
LIMIT 1
slaakso
  • 8,331
  • 2
  • 16
  • 27