ParthModi's avatar

group by but latest record

I have 4 record with same column name and same value. so I do group by that column name but i want Latest record as per created date desc on that groupby my query is

SELECT * FROM tbl_ear_score WHERE `user_id`='".$_REQUEST['user_id']."' AND chapter_id='".$_REQUEST['chapter_id']."' AND best_time != 0 GROUP BY level, type

In this query give me 1st record of that group by but I want latest record as per created date

0 likes
4 replies
Jsanwo64's avatar
Jsanwo64
2 years ago
Best Answer
Level 11

try

SELECT *
FROM tbl_ear_score
WHERE `user_id` = '".$_REQUEST['user_id']."'
  AND chapter_id = '".$_REQUEST['chapter_id']."'
  AND best_time != 0
  AND (level, type, created_date) IN (
    SELECT level, type, MAX(created_date)
    FROM tbl_ear_score
    WHERE `user_id` = '".$_REQUEST['user_id']."'
      AND chapter_id = '".$_REQUEST['chapter_id']."'
      AND best_time != 0
    GROUP BY level, type
  )
Jsanwo64's avatar

or try

SELECT * FROM tbl_ear_score
WHERE `user_id` = '".$_REQUEST['user_id']."'
AND chapter_id = '".$_REQUEST['chapter_id']."'
AND best_time != 0
GROUP BY level, type
ORDER BY created_at DESC
LIMIT 1;
shahr's avatar

To achieve this, you can use a subquery to get the latest record for each group before grouping the results by level and type. Here is an example query:

SELECT t1.* 
FROM tbl_ear_score t1
INNER JOIN (
  SELECT level, type, MAX(created_date) AS max_date
  FROM tbl_ear_score 
  WHERE `user_id`= '".$_REQUEST['user_id']."' AND chapter_id='".$_REQUEST['chapter_id']."' AND best_time != 0 
  GROUP BY level, type
) t2 ON t1.level = t2.level AND t1.type = t2.type AND t1.created_date = t2.max_date
WHERE `user_id`='".$_REQUEST['user_id']."' AND chapter_id='".$_REQUEST['chapter_id']."' AND best_time != 0 
GROUP BY level, type

In this query, the subquery selects the maximum created date for each group of level and type. The main query then joins with this subquery on the level, type, and created date to retrieve the corresponding records. Finally, the results are grouped by level and type to remove duplicate rows.

Note: Make sure to sanitize the user input values properly in the query to prevent SQL injection attacks.

Please or to participate in this conversation.