CODEWITHSUNDEEP
mysql
Aldi Unanto
Aldi Unanto

Reputation: 3682

Get the newest data but only one

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?

Upvotes: 1

Views: 100

Answers (2)

Edper
Edper

Reputation: 9322

Try

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

Upvotes: 1

John Woo
John Woo

Reputation: 263733

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

Upvotes: 4

Related Questions