distinct description

Question

I have the following database:

id | remote_id | title | description
-------------------------------------
1  | 12341234  | test  | test-description
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

I'm trying to select the description (and ONLY the description) of each unique remote_id. The 'winning' description should be the highest id. like this:

id | remote_id | title | description
-------------------------------------
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

using the following code returns each unique remote_id correctly

SELECT DISTINCT(remote_id) FROM table ORDER BY id DESC

but this returns the duplicates

SELECT DISTINCT(remote_id),description FROM table ORDER BY id DESC

How do can I filter distinct values on remote_id, but only select the description with the highest id in my query?

any help would be greatly appreciated!

EDIT 1: formatting

EDIT 2: using following version: 5.7.27-0ubuntu0.18.04.1

Answer 1

One way is to use a Derived Table (subquery) and get all the maximum id values for every unique remote_id value. We can then JOIN this subquery to the original table to get the rows corresponding to highest id.

SELECT 
  t.*
FROM table_name AS t 
JOIN (SELECT 
        remote_id, 
        MAX(id) AS max_id 
      FROM table_name
      GROUP BY remote_id) AS dt 
  ON dt.remote_id = t.remote_id 
     AND dt.max_id = t.id 

---

Another way is to use a Correlated Subquery inside the WHERE clause. We will get the Max() id value for every remote_id and match against the same.

SELECT 
  t.*
FROM table_name AS t 
WHERE t.id = (SELECT MAX(t2.id) 
              FROM table_name AS t2 
              WHERE t2.remote_id = t.remote_id)