Nested query in mysql in messages

Question

This query returns the result in ASC order, Please i need to retrieve the results in DESC order in the nested shape because of other usage OR I need to retrieve the last sent message for each user

SELECT * FROM (SELECT * FROM messages ORDER BY messages.ID DESC ) messages GROUP BY From_User_ID

I suspect what you're looking for is this: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 but your question isn't clear. — Barmar, May 20 '15 at 20:03

Reisclef · Accepted Answer · 2015-05-20T20:02:48.573

1

Repeat your order by in the outer query:

SELECT * FROM (SELECT * FROM messages ORDER BY ID DESC ) messages ORDER BY messages.ID DESC

Edit:

Following the comment, you'd still just reference the table once, and the order by in the outer query:

SELECT * FROM  (SELECT * FROM messages ORDER BY ID DESC )
GROUP BY messages.From_User_ID -- You may have to group by additional fields as you're selecting all
ORDER BY messages.From_User_ID DESC

edited May 20 '15 at 20:02

answered May 20 '15 at 19:55

Reisclef

2,056
1
22
25

I need to make outer group SELECT * FROM (SELECT * FROM messages ORDER BY ID DESC ) messages GROUP BY From_User_ID – Omar Tawba May 20 '15 at 19:57
Are you trying to get the highest ID for each `From_User_Id`? – Barmar May 20 '15 at 20:01
1

@OmarTawba Update your question to show what you're really trying to do. Because the query you posted won't do what you said it does. – Barmar May 20 '15 at 20:02
I want to retrieve the last message that each user sent, thanks for your help – Omar Tawba May 20 '15 at 20:05
Okay, can you please do as @Barmar suggested and edit your question to clarify? Can you also provide sample data? I think I've answered the question posed at the start. I'm guessing from your further comment that you want a: having Max(date) clause but there's no way of telling without seeing the data. – Reisclef May 20 '15 at 20:08
Ah, great! Glad I could at least help point you in the right direction with the SQL! What was your final query? – Reisclef May 20 '15 at 20:25

score 0 · Answer 2 · answered May 20 '15 at 20:07

I am assuming that the "Messages" table has a field called "x". So you want the result of aggregate function sum (as an example of usage of "Group by"):

SELECT t1.From_User_ID, sum(t1.x) as sumOfX 
FROM (
    SELECT * 
    FROM messages
    ) as t1
GROUP BY t1.From_User_ID
ORDER BY ID DESC

You can replace "sum" by "count", "avg", "min" or "max".

Nested query in mysql in messages

2 Answers2