How to get 100 record of each customer in a single query

Question

I have a location table now I have to keep only 100 record for each customer and have to move rest of them to another server.

using this query We can get customers list

SELECT user_id FROM user_location_history WHERE (TIMESTAMPDIFF(DAY,FROM_UNIXTIME(location_date/1000),SYSDATE()) < 30) GROUP BY user_id HAVING COUNT(1) < 100

Now suppose we have a list of customer like

I am trying to write a single query to get all the record for each customer which is greater then 100.

125452
412555
554114
258471

Please suggest ....

How can we know which 100 of them you want? date column? id? — sagi, Mar 07 '16 at 09:02
Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Dylan Su, Mar 07 '16 at 09:03
i want to keep latest 100 records of each user & move rest records into another server. It is basically based on the count of records. — Vishva Nath Singh, Mar 07 '16 at 09:09

score 0 · Answer 1 · answered Mar 07 '16 at 09:33

0

This will get the latest 100 records of each customer

SELECT   user_id, substring_index(group_concat(sample_id ORDER BY sample_id DESC SEPARATOR ','), ',', 100) limit100, FROM     user_location_history  GROUP BY user_id ;

answered Mar 07 '16 at 09:33

Gopalakrishnan

957
8
19

Please find the table structure [Image](http://52.6.142.97/Latlong.png) and provide a details query .... – Vishva Nath Singh Mar 07 '16 at 11:08

score 0 · Answer 2 · answered Mar 07 '16 at 09:55

Try this query it generates a row number for each record :-

set @num := 0, @group := '';
select *
from 
(
   select user_id,
      @num := if(@group = `user_id`, @num + 1, 1) as row_number,
      @group := `user_id` as dummy
  from user_location_history
  WHERE (TIMESTAMPDIFF(DAY,FROM_UNIXTIME(location_date/1000),SYSDATE()) < 30)
) as x 
where x.row_number <= 100;

How to get 100 record of each customer in a single query

2 Answers2