CODEWITHSUNDEEP
mysqlsql
Victor Dodon
Victor Dodon

Reputation: 1874

Select last N unique records mysql

I have a "sessions" table that among other field has the following fields:

int int(11),
user_id int(11),
login_date timestamp,
ip varchar(15)

I want to select for each user the last login_date and the last three unique ips. How can I do this in an elegant way?

I found a solution but it's very very very ugly, ineffective, time consuming and involves a mix o mysql and bash scripting. How can I do this only in MySQL?

P.S. The table has cca 4.3 million rows.

Some sample data: http://sqlfiddle.com/#!2/e9ddd

Upvotes: 1

Views: 612

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Took a lot of time to convert in MYSQL from SQL SERVER

I have work on my Query for better Performance.

select 
user_id,
max(cast(login_date as datetime)),
group_concat(distinct ip order by login_date desc SEPARATOR  ' , ')
from 
(
SELECT 
  user_id,
  login_date,
  ip,
  CASE user_id 
   WHEN @curType 
    THEN @curRow := @curRow + 1 
    ELSE @curRow := 1 AND @curType := user_id 
  END as sequence
FROM sessions, (SELECT @curRow := 0, @curType := '') r
ORDER BY user_id asc,login_date desc
)t
where sequence<4
group by user_id

SQL FIDDLE

Upvotes: 3

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

You can do this: 

SELECT * FROM (
  SELECT 
    user_id
    ,ip, 
    ,login_date
    ,IF(@user_id = SESSION_GRUOPED.user_id, @rownum:=@rownum+1, @rownum:=1 ) as row_id
    ,(@user_id:= SESSION_GRUOPED.user_id) as group_id
  FROM (
    SELECT user_id, ip, max(login_date) login_date FROM SESSION group by user_id, ip order by user_id, login_date desc
  ) SESSION_GRUOPED 
    JOIN  (SELECT @rownum := 0) r
    JOIN  (select @user_id := 0) u
) RESULT
WHERE row_id < 4;

The key of the query is this part

SELECT user_id, ip, max(login_date) login_date FROM SESSION group by user_id, ip order by user_id, login_date desc

Thanks to it you get information about last login on given ip by each user.

Then you wrap this to set the result number in group, then you just limit the result to those are less then 4 as you want only three results for single user.

Note: If you change row_id < 4 into row_id = 1, the you will have the latest login of user.

Upvotes: 0

gmaliar
gmaliar

Reputation: 5479

This would get you the last date for each user_id

SELECT s1.* FROM sessions s1 
LEFT OUTER JOIN sessions s2
ON s1.user_id = s2.user_id AND s1.login_date < s2.login_date
WHERE s2.user_id IS NULL

and this will get you the unique IPs used for each user_id

SELECT user_id, GROUP_CONCAT(DISTINCT(ip)) FROM sessions GROUP BY user_id

then you can mix them, getting it all together would be a bit more heavy on resources but I think it's doable.

good luck!

http://sqlfiddle.com/#!2/bb209/71

Upvotes: 0

Related Questions