MySQL - Finding a user's first referrer?

If you are looking the first referrer by date for each user you can do something like that.

If you are looking the first referrer by date for each user you can do something like that: CREATE TEMPORARY TABLE tmp_hits SELECT `unique` , `date` , `referrer` FROM log_table ORDER BY `date` ASC ; SELECT `unique` , `referrer` FROM tmp_hits GROUP BY `unique`.

Thanks! This seems to work. – Tim Jan 6 at 8:28 Glad to hear it helped – Glide Jan 6 at 8:37.

If you don't have a hit_id field, you'll have to use the pair of (unique, date) as a row identifier. You should be able to get what you are looking for with something like this. SELECT `referrer` FROM `hits` h1 INNER JOIN (SELECT `unique`, MIN(`date`) FROM `hits` GROUP BY `unique`) h2 ON h1.

`unique` = h2. `unique` AND h1. `date` = h2.

`date` GROUP BY `referrer` If you have a primary key you didn't mention, like hit_id, it gets a bit shorter and saves you from the rare case that two hits occur from the same user in the same second: SELECT `referrer` FROM `hits` h1 INNER JOIN (SELECT MIN(`hit_id`) FROM `hits` GROUP BY `unique`) h2 ON h1. `hit_id` = h2. `hit_id` GROUP BY `referrer` In both cases, the last GROUP BY is just to remove dups in your final result set.

Should have clarified - I do have a unique id field. – Tim Jan 6 at 6:57 I'm looking for something that runs without picking a particular user - it should return one referrer per user, and that referrer should be the one with the smallest date (or id) value. – Tim Jan 6 at 7:54 Then the second edited query should work for you.

The inner query will get you the hit_id of the records that represent each user's first hit, the outer query will join with that to get you the referrer for each user's first hit. I'd be curious to know how the performance compares between this and the TEMP TABLE idea. – Umbrella Jan 6 at 13:36.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.