Create a query for get the lasts 5 rows for every users

With this query i get the all notifies for the lasts users of my site that are created them:

    SELECT id,lasts.user_id FROM notifies INNER JOIN 
(SELECT user_id, MAX( created_at ) as last_at
FROM  `notifies` 
GROUP BY user_id
ORDER BY last_at DESC
LIMIT 5) as lasts
ON notif开发者_开发问答ies.user_id = lasts.user_id

I want get only the lasts 5 notifies for these user. How can i modify this query?

thanks

---

SELECT  n.*
FROM    (
        SELECT  u.id,
                COALESCE(
                (
                SELECT  created_at
                FROM    notifies ni
                WHERE   ni.user_id = u.id
                ORDER BY
                        user_id DESC, created_at DESC
                LIMIT 5
                ), CAST('0001-01-01' AS DATE)) AS lt
        FROM    users u
        ) u
JOIN    notifies n
ON      n.user_id <= u.id
        AND n.user_id >= u.id
        AND n.created_at >= u.lt

Create an index on notifies (user_id, created_at) for this to work fast.

Note that n.user_id <= u.id AND n.user_id >= u.id instead of mere n.user_id = u.id should be used to make MySQL use this index efficiently.

---

Using this technique.

select user_id,
       created_at,
       last_at
from   (select user_id,
               created_at,
               last_at,
               @num := if(@group = user_id, @num + 1, 1) as row_number,
               @group := user_id as dummy
        from   `notifies` 
        order by last_at desc) as x
where  row_number <= 5;

---

Reading your sql i think that you have a table named "notifies" like these fields

id | user_id | created_at 

And you require to get the last five entries of a specified user using the created_at field.

SELECT
    id, user_id,created_at 
FROM 
   notifies 
WHERE 
  user_id = ? 
ORDER BY 
  created_at DESC
LIMIT 5;

This SQL selects all the table and but filters to get only the user_id = ? (where ? is the id you are requiring) and next order it by created_at starting with the upper date (the most recet) and finally we crop the SQL to get only the first five elements of this sql.

If i'm wrong in my supposition please tellme, but this is that i think you require reading your question.