Get last update values from MySQL table

Question

I have a mysql table with the following structure and data. I want to show last inserted record on any id.

id    lc_counter       lc_timestamp
1     15               2013-03-01 11:54:43
1     13               2013-03-01 11:48:56
10    7                2013-03-01 11:54:43
10    5                2013-03-01 11:48:56
100   5                2013-03-01 11:54:43
100   3                2013-03-01 11:54:43


SELECT inv_id, lc_counter, lc_timestamp 
FROM link_counter 
group by inv_id
order by inv_id asc, lc_timestamp desc

I want to get this result:

id    lc_counter       lc_timestamp
1     15               2013-03-01 11:54:43
10    7                2013-03-01 11:54:43
100   5                2013-03-01 11:54:43

score 2 · Answer 1 · edited Mar 01 '13 at 10:32

2

Select link_counter.lc_counter, MAX(link_counter.lc_timestamp)
 from link_counter group by link_counter.id

edited Mar 01 '13 at 10:32

Andrew Barber

39,603
20
94
123

answered Mar 01 '13 at 10:13

Dipesh Parmar

27,090
8
61
90

score 0 · Answer 2 · answered Mar 01 '13 at 10:16

0

SELECT
  inv_id, lc_counter, lc_timestamp
FROM
  link_counter A
WHERE
  lc_counter >= ALL(
    SELECT
      lc_counter
    FROM
      link_counter B
    WHERE
      B.inv_id = A.inv_id
  )
ORDER BY
  1

Haven't done this in a while, but that should work.

answered Mar 01 '13 at 10:16

aaaaaa123456789

5,541
1
20
33

chandimak · Answer 3 · 2013-03-01T12:11:57.233

0

If you don't have 2 identical lc_timestamp records for a particular id the following will give you the exact result you want.

select lc.id, lc.lc_counter, lc.lc_timestamp from link_counter lc inner join
 (select id, max(lc_timestamp) as lc_timestamp from link_counter group by id) as lc2 
 on lc.id = lc2.id and lc.lc_timestamp = lc2.lc_timestamp order by lc.id asc, lc.lc_timestamp desc;;

group by itself doesn't return the matching lc_counter. So, it needs to be matched manually. Refer this fiddle.

edited Mar 01 '13 at 12:11

answered Mar 01 '13 at 11:48

chandimak

201
3
17

Dipesh Parmer's and aaaaaa123456789's answers will return expected result for this data set. But it could return erroneous values if data is changed(not underestimating their support). Check changed data set in the fiddle(lc_timestamp top 2 values swapped). This is the best I could do for today. See further how group by works. This [question](http://stackoverflow.com/questions/1425240/select-rows-with-maximum-column-value-group-by-another-column) describes a little. – chandimak Mar 01 '13 at 12:07

Get last update values from MySQL table

3 Answers3

Linked