Problems wording a GROUP BY ranked query

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Problems wording a GROUP BY ranked query

Ila B.

I have a table which looks like this:
Username | prescription_code | birth_date | prescription_date

I have 5 age classifications: 0-14, 15-24, 25-44, 45-64 and 65+.
I want to extract for each year (using date_part) the three most common prescriptions (maximum count) for each age classification (using just the date subtraction and converting years in days).

I would like results to be in the same table, to export it as CSV in an easy way using the IDE (PGAdmin 4, version 3.5 on Windows). I thought about using UNION and CTEs to extract rank, but that would mean writing 5 queries with CTEs. Is there a better way?