Get the newest data but only one

Question

this is the table(followup) :

enter image description here

My question is, I wanted to select the newest data by fol_date for each cust_name inserted. The sample result I've marked with the red arrows. Anyone help?

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — CBroe, Jun 27 '13 at 08:13
Other answerers post their solutions but they failed to mention this is just a common problem of [tag:greatest-n-per-group], which has already well tested and highly [optimized solutions](http://stackoverflow.com/q/8748986/684229). Personally I prefer the [left join solution of Bill Karwin](http://stackoverflow.com/a/8749095/684229) (the [original post with lots of other solutions](http://stackoverflow.com/a/123481/684229)). In case you need last `n` records it is more complicated, but also [optimized and tested solutions exist](http://stackoverflow.com/a/17001909/684229). Also see the tag [ta — Tomas, Jun 27 '13 at 08:27

score 4 · Accepted Answer · answered Jun 27 '13 at 08:13

4

Here's one possible solution.

SELECT  a.*
FROM    tableName a
WHERE   a.fol_date =
        (
            SELECT  MAX(fol_date)
            FROM    tableName b
            WHERE   b.cust_name = b.cust_name
        )

or by using JOIN,

The idea of the subquery is to get the latest fol_date for every cust_name. This will already give you the result you want but if you want to get the whole column within the row, you need to join it to the table itself provided that it match on two conditions, cust_name and fol_date.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  cust_name, MAX(fol_date) fol_date
            FROM    tableName 
            GROUP   BY cust_name 
        ) b ON  a.cust_name = b.cust_name AND
                a.fol_date = b.fol_date

answered Jun 27 '13 at 08:13

John Woo

258,903
69
498
492

it's work but actually, `SELECT cust_name, MAX(fol_date) fol_date FROM tableName GROUP BY cust_name` That's the path of your answer that actually work as well, and same as @Edper said. So I don't need to using `inner join`, Thanks – Aldi Unanto Jun 27 '13 at 08:34
1

If you only want the cust_name and max fol_date then the answer you accepted is fine, but most of the time you need other columns (such as the fol_id in your example data) in which case you need to use the solution posted here. – Kickstart Jun 27 '13 at 08:50
The fact now is, @JW's answer the better. – Aldi Unanto Jun 28 '13 at 01:12

score 1 · Answer 2 · answered Jun 27 '13 at 08:16

1

Try

SELECT cust_name, MAX(fol_date) as LatestDate
FROM FollowUp 
GROUP BY cust_name

answered Jun 27 '13 at 08:16

Edper

9,144
1
27
46

Get the newest data but only one

2 Answers2