[RESOLVED] mysql query distinct getting invalid output

jansky

to all guru

i have this table statistics and has the following field

id | ip | referer | date
1 1.2.7.1 google 2008-4-29
2 1.2.7.1 mysite.php 2008-4-29
3 1.2.7.2 mysite2.php 2008-4-29
4 1.2.7.5 mysite3.php 2008-4-29
5 1.2.7.1 mysite4.php 2008-4-29

now everytime a user visit the site it always insert record , now in the admin

i only want to display the latest activity or track of the user

here is my query

SELECT distinct(ip),customer_id,referer,date_now,id
FROM visit_stat
WHERE date_now = '$date'

now the problem here is .. it display the first row but not the last row of the user

output:
1 1.2.7.1 google 2008-4-29 <--- this should be not the output should be the ID number 5 because that is the latest track
3 1.2.7.2 mysite2.php 2008-4-29 // correct
4 1.2.7.5 mysite3.php 2008-4-29 // correct

bradgrafelman

Try adding an 'ORDER BY id DESC' to the end.

jansky

adding order by does not solve anything
it just sort out

5 1.2.7.1 mysite4.php 2008-4-29
4 1.2.7.5 mysite3.php 2008-4-29

Lars_Berg

select id,ip,referer,date_now
  from visit_stat as vs
 where date_now =
  (select max(date_now)
       from visit_stat
     where visit_stat.ip = vs.ip)

bradgrafelman

You might also try getting rid of the 'DISTINCT' and instead using a 'GROUP BY ip' with the LIMIT clause.

jansky

Lars Berg;10884939 wrote:

select id,ip,referer,date_now
  from visit_stat as vs
 where date_now =
  (select max(date_now)
       from visit_stat
     where visit_stat.ip = vs.ip)

thanks ,

just do some changes
SELECT id, ip, referer, date_now,date_entry,customer_id
FROM visit_stat AS vs
WHERE id = (

						SELECT max( id )
						FROM visit_stat
						WHERE visit_stat.ip = vs.ip
						)
						and date_now='$date'";