How to make POSTGRESQL query based on first 2 parameters and latest third parameter-CodePudding

I have DB:

**client_id  |  Version |  Date(year, month, day)**

I need to make query request for last year(or last 12 months) group by Client_id, Version and latest date(!) for them. For example:

client_id  |  Version |    **LATEST** Date
   23           v2           2022-1-25
   23           v1           2021-3-23
   25           v0           2021-6-23

This is what I have right now:

SELECT client_id, Version, Date 
FROM db_table
WHERE date >= '2022-01-01' AND date < NOW()::DATE
GROUP BY client_id, Version, Date

And I'm getting result for EVERY DAY. If I'm removing DATE from group by, its complaining that Date should be in Group by.

I hope I did describe everything properly. I'm new here, so please let me know if I provide not full info.

Thank you for your time.

CodePudding user response：

To get the most recent full year (i.e. not including the partial current day),

SELECT current_date - interval '1 year'; --this returns 2021-02-07 when run today

To consider today as part of the 12 month period, then:

SELECT current_date - interval '1 year - 1 day'; --this returns 2021-02-08 when run today

So, assuming that you want the former option (you can switch it if you like):

SELECT client_id, "Version", MAX("Date") AS latest_date 
FROM db_table
WHERE "Date" BETWEEN current_date - interval '1 year' AND current_date
GROUP BY client_id, "Version";

p.s. recommend you don't use "Date" as a column name since it's a reserved word, and avoid using upper case letters in column names :)