There are two tables ( orders , chat ) where order_id = chat_id . How can you select only the last entry in the pivot table (sort by date_time field) from the chat table when you select the Select table, given that the chat table can contain multiple values ​​with the same chat_id ?

I fulfill request of a type:

 SELECT o.Name, c.chat_id FROM Orders o LEFT JOIN chat c ON o.order_id=c.chat_id

but displays a variety of values.

limit 1 does not fit because it completely limits the SELECT query and I need to select all values ​​from the orders table and only the last values ​​from the chat table. THOSE. for each o.order_id value, get the last value sorted by date from the chat table

  • one
    And where in your request limit and order by? - Mike
  • The computer does not do what you want, it does what you write - HELO WORD
  • In general, add order by date_time desc limit 1 . - Mike
  • Specify the data schema. You have a confusion with the fields in question. - Ostin

2 answers 2

The specified request is not consistent with the question. As from the attached table the field on which is fulfilled JOIN is selected. The question posed is solved by a query without JOIN at all:

 SELECT o.Name, o.order_id FROM Orders o

I can assume that an error crept into the question, and the sample doesn’t chat.chat_id at all, but, for example, chat.id Then the question makes sense.

In MySQL, this is only available using a subquery (see "Window Functions" in other databases) or by tricky JOINs.

However, in the case when the identifiers of the joined table are filled with auto-increment and sorting by creation date is similar to sorting by identifier (for example, selecting the last from the joined table), it is easiest to use grouping to display identifiers:

 SELECT o.name, max(c.id) FROM orders o LEFT JOIN chat c ON c.order_id = o.id GROUP BY o.id

Related queries on english language resource:

  • I think you have a "c.order_id = c.id" error here ... - nobody
  • @nobody thanks, corrected. - Ostin

Try this:

 select o.* from orders o left join chats c on o.order_id = c.chat_id inner join (select chat_id, max(date_time) as maxi from chats group by chat_id) ma on c.chat_id = ma.chat_id and c.date_time = ma.maxi

If there are no other unimportant fields in the table, you can:

 select o.* from orders o left join (select chat_id, max(date_time) as maxi from chats group by chat_id) ma on o.order_id = ma.chat_id