CODEWITHSUNDEEP
mysql
Naresh
Naresh

Reputation: 2781

Fetching distinct from mysql table is getting wrong result

I am trying to fetch distinct record from mysql table order by visit time which should be latest visit with below query: 

SELECT COUNT(*) as cookie_id, id, lead_id
FROM analytics where lead_id != 0 and user_id = 1
GROUP BY lead_id 
HAVING cookie_id > 1 order by visit_time desc

But with above query i am getting wrong result.

** My Required result initial:**

2054, 2016-02-02 14:19:40
2051, 2016-02-02 13:54:17
2055, 2016-02-02 12:22:16
2039, 2016-02-02 11:46:49

My original data table screen attached.from below table i am trying to get filter result..

enter image description here

Upvotes: 0

Views: 58

Answers (4)

Naresh
Naresh

Reputation: 2781

That was solution for me:

SELECT count(lead_id) as lead_id_count, lead_id, cookie_id, max(id) as max_id,max(visit_time)
FROM analytics where user_id =1 and lead_id != 0 GROUP BY cookie_id,lead_id HAVING lead_id_count > 1 ORDER BY id DESC LIMIT 0 ,10

Upvotes: 0

Jeff
Jeff

Reputation: 9674

This does what you are looking for

select lead_id, cookie_id, max(visit_time) as visit_time
from analytics
where lead_id != 0
and user_id = 1
group by lead_id, cookie_id
order by visit_time desc

be sure that you have indexes on lead_id, user_id and visit_time

Upvotes: 2

CodeGodie
CodeGodie

Reputation: 12132

This is very simple. For you to get your desired result(the required result you mentioned above). All you need to do is use GROUP BY like this:

SELECT lead_id, visit_time
FROM analytics
GROUP BY lead_id

Sample SQL: SQL FIDDLE

Upvotes: 0

Niagaradad
Niagaradad

Reputation: 463

you want GROUPWISE MAXIMUM of a column, an example exists in the manual here

Upvotes: 0

Related Questions