CODEWITHSUNDEEP
phpmysqlsqldatabase
Udit Trivedi
Udit Trivedi

Reputation: 246

Get record from same Table with per user limit

I have a table of posts in which a user will have multiple posts entered with date, so each user can have different number of posts in the table.

I need to fetch last 10 records from table per user, so say there are 3 users with id, 1,2,3 and each have 12,15,20 posts relatively.

So I need to fetch last 10 posts for each user from the table, my table structure is as follows.

id      post_data    created_date           user_id
1       test         2014-01-29 17:49:56    1
2       test         2014-01-28 17:49:56    1
3       test         2014-01-27 17:49:56    1
4       test         2014-01-28 17:49:56    2
5       test         2014-01-29 17:49:56    3
------------
-----------
----------

etc.

So can anyone help me out for a query to fetch last 10 records for each user.

Thanks in advance.

Upvotes: 0

Views: 107

Answers (3)

peterm
peterm

Reputation: 92785

You can do

SELECT id, post_data, created_date, user_id
  FROM
(
  SELECT id, post_data, created_date, user_id,
  (
    SELECT 1 + COUNT(*)
      FROM posts
     WHERE user_id = p.user_id
       AND created_date < p.created_date
  ) rnum
    FROM posts p
) q
 WHERE rnum <= 10
 ORDER BY user_id, created_date DESC;

or

SELECT id, post_data, created_date, user_id
  FROM
(
  SELECT id, post_data, created_date, user_id,
         @n := IF(@u = user_id, @n + 1, 1) rnum, @u := user_id
    FROM posts CROSS JOIN (SELECT @n := 0, @u := NULL) i
   ORDER BY user_id, created_date DESC
) q
 WHERE rnum <= 10
 ORDER BY user_id, created_date DESC;

Here is SQLFiddle demo

Upvotes: 1

Ranjeet Lal Bahadur Maurya
Ranjeet Lal Bahadur Maurya

Reputation: 101

Find the total record of that user and generate offset by subtracting 10 and then can set offset and limit in mysql

Upvotes: 2

Kumaresan Lc
Kumaresan Lc

Reputation: 281

Better to create a stored procedure and create a cursor to walk through the list of users and fetch last 10 records.

Upvotes: 0

Related Questions