CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
pixiesweet44
pixiesweet44

Reputation: 33

SQL - finding max date

I have some data with 2 date fields: record date and purchase date.

Record date is the first day of every month: 

2016-01-01
2016-02-01
2016-03-01

Purchase date is the date that a product is purchased: 

2015-02-14
2016-01-06

What I need to do is take the most recent purchase date for every record date. So NOT the most recent purchase date overall, which is what MAX(purchasedate) gives me. But the max purchase date BEFORE record date.

So my resulting dataset for the examples I have listed above would be: 

Record Date          Most Recent Purchase Date
2016-01-01           2015-02-14
2016-02-01           2016-01-06
2016-03-01           2016-01-06

Upvotes: 0

Views: 3118

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use window function max for find the latest purchase in the order of increasing record_date:

select record_date,
    max(purchase_date) over (
        order by record_date
        ) as latest_purchase_date
from your_table;

Upvotes: 1

Vnge
Vnge

Reputation: 1305

You might be able to try this:

select recordDate, max(purchaseDate) from myDB 
 group by recordDate

I am assuming SQL Server.

Upvotes: 0

Related Questions