Q:

How to get the last day of a month on different items? - SQL

Supposed I have some sample data in table_name_a as below:
    code     val_a   date
   -------------------------
1   00001    500    20191101
2   00001    1000   20191130
3   00002    200    20191101
4   00002    400    20191130
5   00003    200    20191101
6   00003    600    20191130

There are some val_a of code between 20191101 and 20191130, I would like to get the last day value of the month on every code, and my SQL query is as below(need to match Hive and Impla):
SELECT code, max(date) AS date, val_a
    FROM table_a
    WHERE date BETWEEN '20090601'
    AND '20090630'
GROUP BY code, val_a

But above query was wrong(the val_a of code is not the last day of the month)，My expected output as below:
    code    val_a      date
   --------------------------
1   00001    1000   20191130
2   00002    400    20191130
3   00003    600    20191130

Thanks so much for any advice.

A:

We could try using a ROW_NUMBER solution here:
WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY code ORDER BY date DESC) rn
    FROM table_a
    -- WHERE date BETWEEN '20090601' AND '20090630'
    -- your current WHERE clause is dubious
)

SELECT code, date, val_a
FROM cte
WHERE rn = 1;

Note that it is not best practice to be storing dates as text.  That being said, given that you are storing your dates in an ISO format with fixed width, we can still work with these dates in this case.  Also, your current WHERE clause does not make sense, so I commented it out.

