0

There is a table document that contains data as shown below:

Id   member_id  type    date
-----------------------------
1      5         1     2019-07-16
2      4         1     2019-07-12
3      5         2     2019-07-15
4      5         3     2019-07-16
5      3         1     2019-07-14
6      4         2     2019-07-16
7      5         1     2019-07-12
8      4         2     2019-07-13

I'm trying to retrieve one of each type with the last date from a specific member_id.

I've tried the following, as the solution of: Retrieving the last record in each group - MySQL, but it not filter by member_id and show the last of each type from all members:

SELECT d1.*
FROM document d1
  LEFT OUTER JOIN document d2
    ON (d1.type = d2.type AND d1.date < d2.date)
WHERE d2.type IS NULL

I expect the output (in case for filtering by member_id = 5):

Id   member_id  type    date
-----------------------------
 1      5         1     2019-07-16
 3      5         2     2019-07-15
 4      5         3     2019-07-16

but the actual output is:

Id   member_id  type    date
-----------------------------
 1      5         1     2019-07-16
 4      5         3     2019-07-16
 6      4         2     2019-07-16

I am working with doctrine, so if have any solution that also can be passed to DQL or Doctrine Query Build I would appreciate.

lipinf
  • 133
  • 1
  • 2
  • 8
  • You apparently have more than one row with type=3 and a date of '2019-07-16'. You need to use a column or columns that are unique so they can break ties. – Bill Karwin Jul 17 '19 at 01:31
  • It is ok if it have more than one row with type=3 and a date of '2019-07-16' since it return just one of them. The point is to filter return row from a specific member_id. For example: member_id = 5. – lipinf Jul 17 '19 at 01:38

0 Answers0