1

I have the following table structure

id    dateChanged    changeType    changeID

which has the following data

1   2014-04-01  memEdit   205
2   2014-04-01  memEdit   205
3   2014-03-31  memEdit   1
4   2014-04-01  memEdit   1
5   2014-04-01  memEdit   3

Now i want to get the latest record for each change id. So i'd get the records with ids 2,4,5. I've tried grouping them, but its giving me the first of each set (so i'm getting 1,3,5)

Ceri Turner
  • 830
  • 2
  • 12
  • 36
  • possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Strawberry Apr 01 '14 at 21:55

3 Answers3

3

1. OLD not working query

SELECT * FROM `your_table`
GROUP BY `changeID`
ORDER BY `id` DESC

2. Working query

The problem is with MySQL's non-standards craziness.

MySQL does not force you to GROUP BY every column that you include in the SELECT list. As a result, if you only GROUP BY one column but return 4 columns in total, there is no guarantee that the other column values will belong to the grouped changeID record that is returned. If the column is not in a GROUP BY MySQL chooses what value should be returned.

Using the subquery will guarantee that the correct record values are returned every time.

The correct and working query would be:

SELECT `t`.* FROM(
    SELECT * FROM `smdbs_app`
    ORDER BY `id` DESC
) `t`
GROUP BY `changeID`
ORDER BY `id`
Shef
  • 44,808
  • 15
  • 79
  • 90
  • Thats not making any difference, its still selecting the wrong rows – Ceri Turner Apr 01 '14 at 22:10
  • @CeriTurner I see where the problem is. It must have solved half of it so far, but there is still a little to go. Is the `id` column an `autoincrement` one (meaning are the most recent change `id`s always greater than the previous change `id`s for the same group)? – Shef Apr 01 '14 at 22:15
  • yeah, id is an auto increment. id will always be the latest, even if there are multiple entries for a single change id on a single day – Ceri Turner Apr 01 '14 at 22:16
  • @CeriTurner No way. Can you create an [**SQL Fiddle**](http://sqlfiddle.com/) so I can take a look further? – Shef Apr 01 '14 at 22:21
  • @CeriTurner Updated my answer. Your problem should be solved now. – Shef Apr 01 '14 at 22:52
  • This works, but you don't even need a group by if you use a subquery in the where close. Take a gander at my answer and lemme know what you think! – SS781 Apr 02 '14 at 01:44
0

Maybe this is working:

SELECT changeID, id
FROM myTable 
GROUP BY changeID
ORDER BY dateChanged DESC;
frlan
  • 6,950
  • 3
  • 31
  • 72
0

This can be done with a simple subquery:

select * from table t
where t.dateChanged =
(select max(dateChanged)
from table t2
where t2.id = t.id)
;

Let me know if this helps!

SS781
  • 2,507
  • 2
  • 14
  • 17