0
SELECT * FROM msg_messages m 
JOIN msg_status s on m.messageId = s.messageId 
JOIN msg_threads t ON t.threadId = m.threadId 
JOIN users u ON u.userId = s.userId 
WHERE t.threadId = 1 
GROUP BY u.userId ORDER BY `m`.`date` DESC

this is the query which give me always first index of every record. but i want get latest index in each group.. i tried many ways but result is nothing. user: this is without grope by

this is with grope by

this is what i want after group by

this is what giving to me

Rick James
  • 135,179
  • 13
  • 127
  • 222
Awais Ayub
  • 389
  • 3
  • 13
  • this should be return what you expected ? – underscore Nov 18 '15 at 10:27
  • it give nice result but I want last row of every group it give me 1st row of every group by – Awais Ayub Nov 18 '15 at 10:28
  • It's tragic, and speaks volumes about the limitations of this otherwise brilliant forum, that the single-most frequently asked question under the MyQSL tag is so often answered incorrectly (or incoherently), as below :-( – Strawberry Nov 18 '15 at 10:36
  • First of all, it's obviously non-sensical to return all columns in this query. Which ones do you actually want? – Strawberry Nov 18 '15 at 10:38
  • Why GROUP BY when no aggregate functions are involved? Where's the index column you are talking about? – jarlh Nov 18 '15 at 10:41
  • @Strawberry i want the all above mentioned column. – Awais Ayub Nov 18 '15 at 10:53
  • If you want to view all column, then I think group by is not needed. Only use group by if you want to select certain column with some record summarized with aggregate function. I'd like to confirm you the issue, if you don't mind. With "latest index in each group", is it "userId" column you mean with group? and which column you mean with "index"? or it is column index that you mean? – andre_northwind Nov 18 '15 at 10:56
  • @andre_northwind i want just last record of every group not all rows :) – Awais Ayub Nov 18 '15 at 10:58
  • I revised my answer, but nevermind. It didn't worked. sorry. – andre_northwind Nov 18 '15 at 11:03
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. NOTE THAT threads AND users tables are probably redundant for this exercise and can be omitted for simplicty – Strawberry Nov 18 '15 at 11:08
  • See the tag I added. – Rick James Feb 21 '20 at 23:09

3 Answers3

-1

TRY THIS CODE

SELECT * FROM msg_messages m 
JOIN msg_status s on m.messageId = s.messageId 
JOIN msg_threads t ON t.threadId = m.threadId 
JOIN users u ON u.userId = s.userId 
WHERE t.threadId = 1 
GROUP BY u.userId ORDER BY `m`.`date` DESC LIMIT 0,1
Mahendra Salve
  • 96
  • 4
-1

how about this:

SELECT * FROM msg_messages m 
JOIN msg_status s on m.messageId = s.messageId 
JOIN msg_threads t ON t.threadId = m.threadId 
JOIN users u ON u.userId = s.userId 
GROUP BY u.userId 
having max (`m`.`date`)
ORDER BY `m`.`date` DESC

I revised my query from 

having max (`t`.`threadId`)

to

having max (`m`.`date`)
andre_northwind
  • 124
  • 4
-1

you can use LIMIT 1 after DESC then you will get last record. example: DESC LIMIT 1;

A.H
  • 129
  • 2
  • 10