2

I have this following data in my database

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|1  | dede  |  1   |2012-11-07 01:04:26|
|2  | dede  |  10  |2012-11-07 01:05:42|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|4  | baba  |  23  |2012-11-07 05:22:50|
|5  | baba  |  35  |2012-11-07 08:06:57|
|6  | baba  |  50  |2012-11-08 06:32:13|
|7  | rara  |  43  |2012-11-13 06:40:35|

I want to get the last update mod_date of each name :

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|2  | dede  |  10  |2012-11-07 01:04:26|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|6  | baba  |  50  |2012-11-08 06:32:13|
|7  | rara  |  43  |2012-11-13 06:40:35|

i tried this solution but it didnt work(same question)

SELECT no,name,exp,max(date) FROM skill_exp
GROUP BY name

and i get this result from that query

|NO | name  |exp   | mod_date          | 
+---+-------+------+-------------------+
|1  | dede  |  1   |2012-11-07 01:05:42|
|3  | GoGo  |  20  |2012-11-07 03:05:40|
|4  | baba  |  23  |2012-11-07 05:22:50|
|7  | rara  |  43  |2012-11-13 06:40:35|
Community
  • 1
  • 1
Belajar
  • 523
  • 2
  • 5
  • 16

2 Answers2

1

The idea behind the subquery is that it separately gets the latest (maximum) mod_Date for each name. The result of which is then joined back to the original table to get other columns in each row.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT name, MAX(mod_date) maxDate
            FROM tableName
            GROUP BY name
        ) s ON  a.name = s.name AND
                a.mod_date = s.maxDate
John Woo
  • 258,903
  • 69
  • 498
  • 492
1

One more simple way with SELF JOIN

select max(t1.no) NO,t1.name,max(t1.exp),max(t1.mod_date) from tablename t1
inner join tablename t2
on t1.name=t2.name
where t1.mod_date>=t2.mod_date
group by t1.name order by t1.No

SQL_LIVE_DEMO

Mariappan Subramanian
  • 9,527
  • 8
  • 32
  • 33