0

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

  • What is your MySQL server version ? Run SELECT version(); and report the result of this query please. – Madhur Bhaiya Sep 26 at 16:00
  • @MadhurBhaiya using 5.7.27-0ubuntu0.18.04.1 – vanderheyde Sep 26 at 16:02
  • 3
    distinct is not a function, and it applies to all columns in the select list. – mustaccio Sep 26 at 16:04
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)
  • 1
    I used the subquery inside the WHERE clause, and it works like a charm. Thanks! – vanderheyde Sep 27 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.