CODEWITHSUNDEEP
mysql
Get Off My Lawn
Get Off My Lawn

Reputation: 36341

Get all users first visit date

I am trying to get a list of dates and ip addresses, of the users first view. Take this list of 2 days (there will be more ip's in the actual data):

"day"           "ip"
"2014-07-01"    "173.8.118.153"
"2014-07-01"    "173.8.118.153"
"2014-07-01"    "173.8.118.153"
"2014-07-02"    "173.8.118.153"
"2014-07-02"    "173.8.118.153"
"2014-07-02"    "173.8.118.153"
"2014-07-02"    "173.8.118.153"
"2014-07-02"    "173.8.118.153"

I want to return 2014-07-01 for this user's first day. What can I do to get a list of all the users first days?

The query I have here works, but I know it is not correct because if I change order by from asc to desc and vise versa, I still get the same day. Basically this is telling me that it is getting the first day that it finds for this ip, and who knows the data may be backwards in the database for some users. What can I do to guarantee that I am getting their first visit date?

select date(viewed) as day, inet_ntoa(ip) as user_ip
from ad_views
where ad_id = 3058440
and viewed > date_sub(now(), interval 30 day)
group by user_ip order by day;

Upvotes: 0

Views: 69

Answers (4)

Andy Jones
Andy Jones

Reputation: 6275

Since you're already grouping, take the min of the viewed

select date(min(viewed)) as day, inet_ntoa(ip) as user_ip
from ad_views
where ad_id = 3058440
and viewed > date_sub(now(), interval 30 day)
group by user_ip order by day;

Upvotes: 2

JimmyK
JimmyK

Reputation: 1040

SELECT day, ip
FROM MyTable t1 LEFT OUTER JOIN MyTable t2
    ON (t1.ip = t2.ip AND t1.day <= t2.day)

Upvotes: 0

Harsh Gupta
Harsh Gupta

Reputation: 4538

Get MIN() of date:

select MIN(date(viewed)) as day, inet_ntoa(ip) as user_ip
from ad_views
where ad_id = 3058440
group by user_ip;

Upvotes: 2

ObiWanShanobi
ObiWanShanobi

Reputation: 442

Select min(date_col) from table_name

This documentation could prove useful to your search

Upvotes: 2

Related Questions