CODEWITHSUNDEEP
mysqlselectlimit
jao
jao

Reputation: 1194

MYSQL - Select most recent of column type entry

I have a table with columns:
s_Id (primary int), sup_type (int), sup_date (datetime), sup_req (int)

Values in sup_type so far range from 0-5.

How can I extract the most recent entry (by sup_date) of each sup_type (0-5).

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*  s_Id   *  sup_type   *        sup_date         *  sup_req  *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*    1    *      0      *   2012-06-15 10:13:21   *     4     *
*    2    *      0      *   2012-06-15 11:22:01   *     4     *
*    3    *      1      *   2012-06-15 13:23:32   *     4     *
*    4    *      2      *   2012-06-16 08:04:29   *     4     *
*    5    *      1      *   2012-06-16 16:23:24   *     4     *
*    6    *      1      *   2012-06-17 13:14:05   *     4     *
*    7    *      3      *   2012-06-18 13:37:55   *     4     *
*    8    *      4      *   2012-06-19 13:21:52   *     4     *
*    9    *      4      *   2012-06-20 16:15:19   *     4     *
*   10    *      5      *   2012-06-20 16:17:37   *     4     *
*   11    *      0      *   2012-06-20 16:21:53   *     4     *
*   12    *      1      *   2012-06-20 16:28:13   *     4     *
*   13    *      3      *   2012-06-21 12:23:29   *     4     *
*   14    *      3      *   2012-06-22 07:26:41   *     4     *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

I want to extract

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*  s_Id   *  sup_type   *        sup_date         *  sup_req  *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*    4    *      2      *   2012-06-16 08:04:29   *     4     *
*    9    *      4      *   2012-06-20 16:15:19   *     4     *
*   10    *      5      *   2012-06-20 16:17:37   *     4     *
*   11    *      0      *   2012-06-20 16:21:53   *     4     *
*   12    *      1      *   2012-06-20 16:28:13   *     4     *
*   14    *      3      *   2012-06-22 07:26:41   *     4     *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Thanks.

Upvotes: 1

Views: 339

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

SELECT
    b.*
FROM
    (
        SELECT sup_type, MAX(sup_date) AS maxsupdate
        FROM tbl
        GROUP BY sup_type
    ) a
INNER JOIN
    tbl b ON 
        a.sup_type = b.sup_type AND 
        a.maxsupdate = b.sup_date
ORDER BY
    b.s_Id

Upvotes: 4

Related Questions