CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Johnny Old
Johnny Old

Reputation: 23

Query to select from multiple tables MySQL

I have two tables. My task is to choose the last person comment.

db_user (db_user_id, name, balance)

db_comment (db_comment_id, db_user_id, text)

My query:

SELECT db_user.name,db_comment.text
FROM db_user INNER JOIN db_comment ON db_user.db_user_id = db_comment.db_user_id
ORDER BY db_comment.db_user_id DESC

Tried to use LIMIT but failed.

A table with values ​​has already been created here: http://sqlfiddle.com/#!9/badaf/14

My data sampling should receive the last comment (db comment.text) from each person (db_user.name).

Сondition, you cannot add new fields.

Upvotes: 2

Views: 249

Answers (5)

Raymond Nijland
Raymond Nijland

Reputation: 11602

Other approach without the need to use MAX(db_comment_id) or GROUP BY db_user_id and complety works by filtering

Query

SELECT 
   db_user.name
 , db_comment1.text
FROM  
 db_comment db_comment1
LEFT JOIN 
 db_comment db_comment2
ON        
    db_comment1.db_user_id = db_comment2.db_user_id 
  AND        
    db_comment1.db_comment_id < db_comment2.db_comment_id
INNER JOIN 
 db_user
ON
 db_comment1.db_user_id = db_user.db_user_id
WHERE
 db_comment2.db_user_id IS NULL

Note: Using db_comment1.db_comment_id > db_comment2.db_comment_id instead would do MIN(db_comment_id) yes the operator direction can feel pretty bit counterintuitive and it is very easy to get wrong and write the wrong one (that's why i needed to edit mine answer...) , see demo..

Result

|  name |           text |
|-------|----------------|
|  Ivan |  Message3 ivan |
|  Petr |  Message3 Petr |
| Artur | Message2 Artur |
|  John |  Message2 John |

Performance note: it needs to have the INDEX(db_user_id, db_comment_id) on the db_comment table otherwise it will not be very fast. If you have that index, MySQL should be able to handle (very) large tables when running this query..

see demo

Upvotes: 0

rohit-s
rohit-s

Reputation: 67

Try it -

SELECT db_user.name,db_comment.text
FROM db_user 
INNER JOIN db_comment 
ON db_user.db_user_id = db_comment.db_user_id
ORDER BY db_user.db_user_id DESC, db_comment.db_comment_id desc
limit 1

OR

SELECT db_user.name,db_comment.text
FROM db_user
JOIN (
  select db_comment_id as maxId, db_user_id, text
  from db_comment
  order by db_comment_id desc
  limit 1
) as db_comment ON db_comment.db_user_id = db_user.db_user_id
ORDER BY db_user.db_user_id DESC`

if the limit is not working then you can use the MAX function and make join self table

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution using a additional JOIN:

SELECT dbu.name, dbc.text
FROM db_user dbu INNER JOIN (
  SELECT MAX(db_comment_id) AS db_comment_id, db_user_id
  FROM db_comment 
  GROUP BY db_user_id 
) dbc_max ON dbu.db_user_id = dbc_max.db_user_id
INNER JOIN db_comment dbc ON dbu.db_user_id = dbc_max.db_user_id 
  AND dbc.db_comment_id = dbc_max.db_comment_id
ORDER BY dbu.db_user_id DESC

... or using a sub-select directly on the SELECT:

SELECT dbu.name, (
  SELECT `text` 
  FROM db_comment dbc 
  WHERE dbu.db_user_id = dbc.db_user_id 
  ORDER BY dbc.db_comment_id DESC 
  LIMIT 1
) AS `text`
FROM db_user dbu
ORDER BY dbu.db_user_id DESC

demo on dbfiddle.uk

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

The last comment is the one with the highest ID. So, make sure there doesn't exist a higher one for the user:

SELECT
  u.name,
  c.text
FROM db_user u
JOIN db_comment c ON c.db_user_id = u.db_user_id
                  AND NOT EXISTS
                  (
                    SELECT *
                    FROM db_comment c2
                    WHERE c2.db_user_id = c.db_user_id
                    AND c2.db_comment_id > c.db_comment_id
                  );

Or work with a list of highest comment IDs per user:

SELECT
  u.name,
  c.text
FROM db_user u
JOIN db_comment c ON c.db_user_id = u.db_user_id
                  AND (c.db_user_id, c.db_comment_id) IN
                  (
                    SELECT db_user_id, MAX(db_comment_id)
                    FROM db_comment
                    GROUP BY db_user_id
                  );

As of MySQL 8 you can also use window function. E.g.:

SELECT
  u.name,
  c.text
FROM db_user u
JOIN 
(
  SELECT
    db_user_id,
    text,
    ROW_NUMBER() OVER (PARTITION BY db_user_id ORDER BY db_comment_id DESC) AS rn
  FROM db_comment
) c ON c.db_user_id = u.db_user_id AND c.rn = 1;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c2c67cdba80a992b593e4c74201fa61

Upvotes: 0

GMB
GMB

Reputation: 222432

I would use a correlated subquery for filtering. In many cases, this is approach that has the best performance, especially with an index on db_comment(db_user_id, db_comment_id):

select u.name, c.text
from 
    db_user u
    inner join db_comment c on c.db_user_id = u.db_user_id
where c.db_comment_id = (
    select max(c1.db_comment_id)
    from db_comment c1
    where c1.db_user_id = c.db_user_id
)

This assumes that the last comment is the one that has the highest db_comment_id.

Updated demo on DB Fiddle

name    text
Ivan    Message3 ivan
Petr    Message3 Petr
Artur   Message2 Artur
John    Message2 John

Upvotes: 0

Related Questions