I have the following query:
select * from tblreviews WHERE Estado=1 group by Website ORDER BY fchFecha DESC limit 5
In which only the part of the group by works for me but it does not order the result in the most recent date.
Thanks for your contributions.
Dear, you must do it with a subquery:
SELECT * FROM
(SELECT * FROM tblreviews WHERE Estado=1 ORDER BY fchFecha DESC) T
GROUP BY T.Website LIMIT 5
In this way you order the conditioned result, and then you group it.
table:
result:
Try this:
SELECT t.* FROM tblreviews AS t
WHERE t.Estado = 1
AND t.id = (SELECT tt.id FROM tblreviews AS tt WHERE tt.Website=t.Website ORDER BY tt.fchFecha DESC LIMIT 1)
ORDER BY t.Website
LIMIT 5
We search, for each Website with Estado = 1 , only the one that matches the record with the highest date (that's why we use descending order).
Eye: It is essential the LIMIT 1 in the subquery, otherwise we will get an error or unforeseen results.
It is necessary to clarify that this query does not take into account the value of Estado on the date used for the ordination, for this the query should be:
SELECT t.* FROM tblreviews AS t
WHERE t.Estado = 1
AND t.id = (SELECT tt.id FROM tblreviews AS tt WHERE tt.Website=t.Website AND t.Estado = 1 ORDER BY tt.fchFecha DESC LIMIT 1)
ORDER BY t.Website
LIMIT 5
This would only take into account, both to show and to establish which date to use, the tblreviews with Estado = 1 , the difference is subtle but will influence the result.
Try it with the function MAX like this:
select * from tblreviews WHERE Estado=1 group by Website
ORDER BY MAX(fchFecha) DESC limit 5