Last 10 Visitors

Databases
1

In a user’s profile, I show the “Last 10 Visitors”.

Over time, the requirements changed, and so my original has become rather messy.

I am trying rewrite things, but the results aren’t quite correct.

Here is my table…

visitor_log


id
member_viewed_id
visitor_id
created_on

First, to verify who has visited my profile, I wrote this query…


SELECT visitor_id, created_on
FROM visitor_log
WHERE member_viewed_id = 19
ORDER BY created_on DESC

And the (abridged) results were…


11 	2014-06-10 21:14:10
25 	2014-06-08 20:11:27
25 	2014-06-08 20:11:16
25 	2014-06-08 20:10:53
25 	2014-06-08 20:10:32
25 	2014-06-08 20:09:04
:
:
1 	2014-06-08 19:28:25
1 	2014-06-08 19:18:24
25 	2014-06-08 19:18:17
25 	2014-06-08 19:17:57
:
:
21 	2014-01-25 19:07:44
20 	2014-01-25 19:06:50
12 	2014-01-25 19:05:30
11 	2014-01-25 19:04:53
35 	2014-01-25 18:39:00
13 	2014-01-25 18:37:15
12 	2014-01-25 18:36:43
11 	2014-01-25 18:35:43
:
:

So far, so good…

But when I run my new query…


SELECT DISTINCT visitor_id
FROM visitor_log
WHERE member_viewed_id = 19
ORDER BY created_on DESC
LIMIT 10

I get these results…


visitor_id (UK2)(FK) VisitorID=1 for Non-Members 	
58
51
50
25
38
13
12
11
1
21

Why did the order change?

When I collapse things using DISTINCT, I would expect the sort order (i.e. DESC) to be the same.

It’s not that big of a deal, I suppose, but to me it is more intuitive when a user looks in his/her profile if he/she see “Last 10 Visitors” in either Newest-to-Oldest or Oldest-to-Newest order.

What am I doing wrong?

Sincerely,

Debbie

2

The ORDER BY isn’t being evaluated on the latest created_on date, it’s being evaluated on whatever created_on date is attached to the record you’re pulling using the DISTINCT. Which is basically random as far you’re concerned in this query.


SELECT visitor_id
FROM visitor_log
WHERE member_viewed_id = 19
GROUP BY visitor_id
ORDER BY MAX(created_on) DESC
LIMIT 10

Use the GROUP BY and MAX(created_on) instead of DISTINCT.

3

Oh, thanks!

I sorta was thinking the same thing you said above about DISTINCT.

While your suggestion seems better, is this what I want?

Because your query groups things first, couldn’t that throw off the order of visitors as well?

Just asking.

Maybe I am making this more complicated than it needs to be…

To you, what is the logical way to show visitors?

Sincerely,

Debbie

4

what happened when you tested it? ™ © ® :smiley:

5

Poor Rudy… Came up with a catchy slogan, and doesn’t even understand it!

When I tested it, it looks like the order is right, but at the time of my response, I wasn’t sure how things work internally.

If you look at the query, you GROUP BY then you ORDER BY which physically would mess things up.

After thinking about things, I am thinking maybe what MySQL does is find the MAX of v.created_on for a given user, and then it collapse things into one record? :-/

If that is how it works internally - read “Testing won’t necessarily make that obvious…” - then I guess @mawburn ; is right on.

Sincerely,

Debbie

6

poor debbie… all these years using sql, and she ~still~ doesn’t understand how it works

7

That’s why I have you… :wink:

8

I actually tested it in SQLFiddle just to be 100% sure I was right.

I should have linked that earlier, but I just did it because I tend to second guess myself alot. lol