# Moving MAX and MIN calculation into a sub query to preserve the full result set in the outer query

The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating

The problem with the query is that it only give 1 row result instead of showing all of the posts.

I think the problem is with using MAX() and MIN() without separating it from the LEFT JOIN query used to get the SUM() of the Rating and the Views.

``````SELECT
p.id
, p.date
, p.title
, r.module
, r.module_id
, IFNULL(v.total_views,0)  AS views
, r.total_rating           AS rating

#the following formula calculates the Score 10*(MAX(points)-(points))/(MAX(points)-MIN(points))

, round((round(10-(((PD.MaxDate-p.date)/(PD.MaxDate-PD.MinDate))*10), 3) + round(10-(((MAX(v.total_views)-v.total_views)/(MAX(v.total_views)-MIN(v.total_views)))*10), 3) + round(10-(((MAX(r.total_rating)-r.total_rating)/(MAX(r.total_rating)-MIN(r.total_rating)))*10), 3))/3, 3) AS Score

FROM  posts p

LEFT  JOIN ( SELECT ra.module_id
, ra.module   AS module
, SUM(ra.ilike)    AS total_rating
FROM rates ra
WHERE ra.module = 'posts'
GROUP
BY ra.module_id
) r ON r.module_id = p.id

LEFT  JOIN ( SELECT pv.post_id
, SUM(1)    AS total_views
FROM posts_views pv
GROUP
BY pv.post_id
) v ON v.post_id = p.id

JOIN (SELECT MIN(date) AS MinDate, MAX(date) AS MaxDate FROM posts) PD

ORDER BY Score DESC
``````

Since you have aggregate functions in your SELECT clause, you need to specify what groups to apply them to. If you add:

``````GROUP BY   p.id
, p.date
, p.title
, v.total_views
, r.total_rating
, r.module
, r.module_id
``````

before ORDER BY in your query, you will get 1 row per such group.

Without a GROUP BY clause, MySQL will randomly pick 1 row for you. This is a well-known bug in older MySQL versions.