How To Display Value by Max Date / Last Date of Year

Question

I need a query to display a value (saldo) following max date / the last date of year, but I have tried but it and it's always showing a value from the first date.

Here is my query:

SELECT saldo, MAX(tgl_lap) FROM laporan GROUP BY DATE_FORMAT(tgl_lap,'%Y','%m') ORDER BY DATE_FORMAT(tgl_lap,'%Y','%m') DESC

my result:

And my expectated result should be only showing values like in the red box below:

Your `GROUP BY` and `SELECT` are inconsistent. Your query should result in an error. — Gordon Linoff, Oct 06 '20 at 19:29
You group by year and month, but you want one result row per year, so you should group by year only. Then you want the `max(tgl_lap)` and `the max(saldo)`. Or can the row with the `max(tgl_lap)` not have the maximum saldo? Then you'd want the saldo of the `max(tgl_lap)` row, I guess? — Thorsten Kettner, Oct 06 '20 at 19:54
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Oct 06 '20 at 20:35

score 0 · Answer 1 · answered Oct 06 '20 at 19:24

0

WITH cte AS ( 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY YEAR(tgl_lap) ORDER BY tgl_lap DESC) rn
    FROM laporan 
    )
SELECT *
FROM cte
WHERE rn = 1

answered Oct 06 '20 at 19:24

Akina

39,301
5
14
25

Can those query used for select params? because i need to sum another values and make the query have you gived to me as another params, hmm how do i say.. examples: SELECT sum(value1), sum(value2), [your query gived to me] from laporan group by tgl_lap – FF.Dantiano Oct 06 '20 at 19:38
1

@FF.Dantiano Does THIS query gives the output which matches THIS task? If not - tell what's wrong. But if you have another question then create new topic. – Akina Oct 06 '20 at 19:41
2

I don't understand the comment / additional question either. It looks to me that this query does exactly what's required (picking the last rows per year). No upvote from me though, because I don't like code-only answers. – Thorsten Kettner Oct 06 '20 at 19:57

Nasir Abbas · Answer 2 · 2020-10-07T01:32:22.723

0

select * 
From laporan t
where t.tgl_lap = 
(
  select max(a.tgl_lap) 
  from laporan a 
  where year(t.tgl_lap) = year(a.tgl_lap)
)

According to the sample data, considering there would be only one record per date, the sub query will return max date of the year and main query will return complete record of the last or max date of the year.

edited Oct 07 '20 at 01:32

answered Oct 06 '20 at 19:48

Nasir Abbas

86
8

5

While the OP does group by month and year, their task description suggests they want one result row per year, really. And please don't post code-only answers. Explain what your query does. – Thorsten Kettner Oct 06 '20 at 20:01

How To Display Value by Max Date / Last Date of Year

2 Answers2