I know the wording of my title is vague so I will try to clarify my question.

tasks_table

The table I am working with has more fields than this, but, these are the ones that are pertinent to the question. The task type can be either A or B.

What I am currently trying to do is get a result set that contains, at max, two tasks per user_id, one of task type A and one of task type B, that have been completed in the past 7 days. For example, the set the query should generate the following result set:

There is a possibility that a user may have only done tasks of one type in that time period, but, it is guaranteed that any given user will have done atleast one task within that time. My question is it possible to create a query that can return such a result or would I have to query for a more generalized result and then trim the set down through logic in my JPA?

CodePudding user response：

To select the most recent task for a given user_id and task_type within the last 7 days from now, if exists, you can try this :

SELECT DISTINCT ON (t.user_id, t.task_type) t.*
  FROM tasks_table AS t
 WHERE t.completed_date >= current_date - interval '7 days'
 ORDER BY t.user_id, t.task_type, t.completed_date DESC