CODEWITHSUNDEEP
phpmysql
user2329361
user2329361

Reputation: 13

MySQL group by value and max date

I need to extract the last character of columns result_1 and result_2 and group them so I have a list from 0 to 9 indicating when it was generated. In the folowing code I managed to obtain a list from 0 to 9 but the date is the earliest date in the table and not the latest.

$sql = "SELECT * FROM ((SELECT RIGHT(result_1, 1) AS results, date 
    FROM act_results ORDER BY date ASC) 
    UNION ALL (SELECT RIGHT(result_2, 1) AS results, date 
    FROM act_results)) s WHERE results != ''";

$sql .= " GROUP BY results";

$table = mysql_query($sql);

while ($row = mysql_fetch_array($table)) {
   echo $row['results']." (".$row['date'].") <br>";
}

Any ideas how to fix it? Thanks in advance.

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use group by with max():

SELECT results, max(date)
FROM ((SELECT RIGHT(result_1, 1) AS results, date
       FROM act_results
      ) UNION ALL
      (SELECT RIGHT(result_2, 1) AS results, date
       FROM act_results
      )
     ) s
WHERE results <> ''
GROUP BY results;

Upvotes: 1

Related Questions