CODEWITHSUNDEEP
sqlmysqlgreatest-n-per-group
smont
smont

Reputation: 1348

Select the newest entry based on other entries with the same values?

Lets say I have a table with some data like the following:

ID     text     OtherID
_______________________
6     text1       24
7     text2       24
8     text3       24
9     text1       25
10    text2       25

As you can see I have multiple entries with the same OtherID. what would be an sql statement that would select only the newest ID from all of the entries with the same OtherID. this is being done in MySQL if that matters.

for example I want it to output:

ID     text     OtherID
_______________________
8      text3       24
10     text2       25

Upvotes: 0

Views: 107

Answers (2)

Rob Van Dam
Rob Van Dam

Reputation: 7970

If you want ONLY the newest id (suggested by your question, but not the example), then just use

SELECT OtherID, MAX(ID) FROM table GROUP BY OtherID

but if you want any other columns, then OMG Ponies' answer is what you want. This is because selecting the other columns (such as text) is not guaranteed to return values corresponding to the same row as the ID returned by MAX(ID).

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

Use:

SELECT DISTINCT
       x.id,
       x.text,
       x.otherid
  FROM TABLE x
  JOIN (SELECT t.otherid,
               MAX(t.id) 'max_id'
          FROM TABLE t
      GROUP BY t.otherid) y ON y.otherid = x.otherid
                           AND y.max_id = x.id

Upvotes: 3

Related Questions