SQL - How to select the latest date of every month?

Question

I have data set that looks something like this:

|---------------------|------------------|------------------|
|          ID         |        Date      |       Cost       |
|---------------------|------------------|------------------|
|          1          |       9/15/20    |       $500       |
|---------------------|------------------|------------------|
|          1          |       9/30/20    |       $600       |
|---------------------|------------------|------------------|
|          1          |       10/2/20    |       $400       |
|---------------------|------------------|------------------|
|          2          |       10/10/20   |       $1,000     |
|---------------------|------------------|------------------|
|          3          |       9/29/20    |       $600       |
|---------------------|------------------|------------------|
|          3          |       10/5/20    |       $400       |
|---------------------|------------------|------------------|
|          3          |       10/6/20    |       $800       |
|---------------------|------------------|------------------|
|          3          |       10/10/20   |       $200       |
|---------------------|------------------|------------------|

Using SQL Runner in Looker, I want to keep only the rows of the latest date available in each month for each ID. So my example table should end up like this:

|---------------------|------------------|------------------|
|          ID         |        Date      |       Cost       |
|---------------------|------------------|------------------|
|          1          |       9/30/20    |       $600       |
|---------------------|------------------|------------------|
|          1          |       10/2/20    |       $400       |
|---------------------|------------------|------------------|
|          2          |       10/10/20   |       $1,000     |
|---------------------|------------------|------------------|
|          3          |       9/29/20    |       $600       |
|---------------------|------------------|------------------|
|          3          |       10/10/20   |       $200       |
|---------------------|------------------|------------------|

Please tag your question with the database you are running: mysql, oracle, sqlserver...? Date functions are highly vendor-specific. — GMB, Oct 12 '20 at 16:10
and what if ties occur? I mean, if there are more than one record with the same latest date for any of the months. — Barbaros Özhan, Oct 12 '20 at 16:23
Does this answer your question? [Return just the last day of each month with SQL](https://stackoverflow.com/questions/5866054/return-just-the-last-day-of-each-month-with-sql) — Daniel Widdis, Oct 12 '20 at 16:50

score 2 · Answer 1 · edited Dec 02 '20 at 12:26

2

The following should run on just about any database:

select id, month(date), max(date) as latest_date_in_month
from <TABLE>
group by id, month(date)
order by id, month(date)

edited Dec 02 '20 at 12:26

Tamás Sengel

55,884
29
169
223

answered Dec 02 '20 at 12:04

Krulwich

41
9

This won't give the `cost` column like OP asked, but you can then join this table back with the original table to add the cost. – DarkHark Feb 15 '23 at 00:34

score 0 · Answer 2 · answered Oct 12 '20 at 16:11

0

You can use row_number() if your DBMS supports it

select id, date, cost from
(
  select id, date, cost,
         row_number() over(partition by id order by date desc) as rn
from tablename
)A where rn<=2

answered Oct 12 '20 at 16:11

Fahmi

37,315
5
22
31

So this returns the latest 2 entries for each ID, I would need the last entry for each month for each ID – Mackay Young Oct 15 '20 at 22:42

SQL - How to select the latest date of every month?

2 Answers2