Query ordered by data from another table

I'm not sure how I (or if) can create this query and would appreciate
some insight.

I am recording visits to a particular page at my web site using a table
(`visits`) with three columns.
Table 1 -`visits' (3 columns)
1 - visits_id # auto_incr
2 - ip_address
3 - date_time

When an ip_address (visitor) visits more than 25 times in less than half
an hour I INSERT that into another table 'over_limit', which has four
columns.

Table 2 -`over_limit` (3 columns)
over_limit_id # auto_incr
ip_address
modified_date # timestamp = date this ip_address last exceeded limit

(Note: if the same ip_address does it again (25 visits in under half an
hour), its entry is UPDATED.)

Here's the query I don't know how to do. I want display the
`over_limit` ip_addresses that have been inserted or updated within the
last two weeks, but ordered by their last visit in `visits`. I know how
to select `'visits` entries that are less that two weeks old, but I do
not know how to order them as I'd like.

Phrased in pseudo code, I want to

(var $two_weeks_ago = timestamp of two weeks ago from right now)

SELECT ip_address FROM 'over_limit` WHERE modified_date > $two_weeks_ago
ORDER BY the date_time of the latest visit (for the selected ip_address)
in table `visits`

Any help would be appreciated.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

Good grief. I have a minor, but confusing typo:

Chuck Anderson wrote:
> ......

> When an ip_address (visitor) visits more than 25 times in less than half
> an hour I INSERT that into another table 'over_limit', which has four
> columns.
>

Should be ..... "which has *three* columns."

> Table 2 -`over_limit` (3 columns)
> over_limit_id # auto_incr
> ip_address
> modified_date # timestamp = date this ip_address last exceeded limit
>

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

On 13 May, 23:30, Chuck Anderson <websiteaddr...@seemy.sig> wrote:
> Good grief. I have a minor, but confusing typo:
>
> Chuck Anderson wrote:
> > ......
> > When an ip_address (visitor) visits more than 25 times in less than half
> > an hour I INSERT that into another table 'over_limit', which has four
> > columns.
>
> Should be ..... "which has *three* columns."
>
> > Table 2 -`over_limit` (3 columns)
> > over_limit_id # auto_incr
> > ip_address
> > modified_date # timestamp = date this ip_address last exceeded limit
>
> --
> *****************************
> Chuck Anderson • Boulder, CO
> http://www.CycleTourist.com
> Nothing he's got he really needs
> Twenty first century schizoid man.
> ***********************************

You have a basic flaw in this. You state "ip_address (visitor)". IP
Address does not equal visitor. With NAT routers and proxy servers,
which are extremely prevalent today, you will find many many visitors
all coming from the same IP address. I am sitting at a company at the
moment that has about 8000 people, all accessing the internet through
the same proxy. 8000 potential visitors all with the same IP address.
This is repeated all round the world.

In order to solve your query, you need to JOIN the 2 tables. But there
seems little point in doing that since the data in the tables is
meaningless within the context that you have outlined.

Captain Paralytic wrote:
> On 13 May, 23:30, Chuck Anderson <websiteaddr...@seemy.sig> wrote:
>
>> Good grief. I have a minor, but confusing typo:
>>
>> Chuck Anderson wrote:
>>
>>> ......
>>> When an ip_address (visitor) visits more than 25 times in less than half
>>> an hour I INSERT that into another table 'over_limit', which has four
>>> columns.
>>>
>> Should be ..... "which has *three* columns."
>>
>>
>>> Table 2 -`over_limit` (3 columns)
>>> over_limit_id # auto_incr
>>> ip_address
>>> modified_date # timestamp = date this ip_address last exceeded limit
>>>
>>
>
> You have a basic flaw in this. You state "ip_address (visitor)". IP
> Address does not equal visitor. ...........

Yes, yes, yes. I am very well aware of what all of that. But your
point is also irrelevant to my application. It's a small application -
not heavily used, and based an entire year of looking at (and
understanding) this data almost daily, I have found it to be 99% useful
and correct.

Soooooooooooo ........

Perhaps back to my question; how to do this query?

[relevant info reinserted]

>> I am recording visits to a particular page at my web site using a table
>> (`visits`) with three columns.
>> Table 1 -`visits' (3 columns)
>> 1 - visits_id # auto_incr
>> 2 - ip_address
>> 3 - date_time
>>
>> When an ip_address (visitor) visits more than 25 times in less than half
>> an hour I INSERT that into another table 'over_limit', which has four
>> columns.
>>
>> Table 2 -`over_limit` (3 columns)
>> over_limit_id # auto_incr
>> ip_address
>> modified_date # timestamp = date this ip_address last exceeded limit
>>
>> (Note: if the same ip_address does it again (25 visits in under half an
>> hour), its entry is UPDATED.)
>>
>> Here's the query I don't know how to do. I want display the
>> `over_limit` ip_addresses that have been inserted or updated within the
>> last two weeks, but ordered by their last visit in `visits`. I know how
>> to select `'visits` entries that are less that two weeks old, but I do
>> not know how to order them as I'd like.
>>
>> Phrased in pseudo code, I want to
>>
>> (var $two_weeks_ago = timestamp of two weeks ago from right now)
>>
>> SELECT ip_address FROM 'over_limit` WHERE modified_date > $two_weeks_ago
>> ORDER BY the date_time of the latest visit (for the selected ip_address)
>> in table `visits`
>>
>

> In order to solve your query, you need to JOIN the 2 tables.

I am aware of JOINS and use them quite a lot these days, but I do not
see how I can use a JOIN for this query (without creating a huge result
set - and one which also does not accomplish my goal).

Here is how I would envision the (flawed) JOIN:

SELECT over_limit.ip_address
FROM 'over_limit`
JOIN `visits` USING (ip_address)
WHERE over_limit.modified_date > $two_weeks_ago
ORDER BY visits.date_time DESC

But using that I get *every* entry in visits for the matching ip
address's. And they are not ordered as I want. I don't want all of the
rows the above JOIN returns, ... and I want to ORDER the rows by the
MAX(date_time) in `visits` for each matching ip address. I have tried
'GROUP BY ip_address', and I still do not get the results I want.

What I want to know is; how can I can order these results by the
MAX(visits.date_time) for each matching ip_address?

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

On Tue, 13 May 2008 17:27:45 -0600, Chuck Anderson wrote:
> I'm not sure how I (or if) can create this query and would appreciate
> some insight.
>
> I am recording visits to a particular page at my web site using a table
> (`visits`) with three columns.
> Table 1 -`visits' (3 columns)
> 1 - visits_id # auto_incr
> 2 - ip_address
> 3 - date_time
>
> When an ip_address (visitor) visits more than 25 times in less than half
> an hour I INSERT that into another table 'over_limit', which has four
> columns.
>
> Table 2 -`over_limit` (3 columns)
> over_limit_id # auto_incr
> ip_address
> modified_date # timestamp = date this ip_address last exceeded limit
>
> (Note: if the same ip_address does it again (25 visits in under half an
> hour), its entry is UPDATED.)
>
> Here's the query I don't know how to do. I want display the
> `over_limit` ip_addresses that have been inserted or updated within the
> last two weeks, but ordered by their last visit in `visits`. I know how
> to select `'visits` entries that are less that two weeks old, but I do
> not know how to order them as I'd like.
>
> Phrased in pseudo code, I want to
>
> (var $two_weeks_ago = timestamp of two weeks ago from right now)
>
> SELECT ip_address FROM 'over_limit` WHERE modified_date > $two_weeks_ago
> ORDER BY the date_time of the latest visit (for the selected ip_address)
> in table `visits`
>
> Any help would be appreciated.

SELECT a.foo, a.bar
FROM table1 a JOIN table2 b on a.foo = b.baz
WHERE ....
ORDER BY b.quux

Short answer: your order-by criteria don't have to be selected for
output.

--
17. When I employ people as advisors, I will occasionally listen to their
advice.
--Peter Anspach's list of things to do as an Evil Overlord

Peter H. Coffin wrote:
> On Tue, 13 May 2008 17:27:45 -0600, Chuck Anderson wrote:
>
>> I'm not sure how I (or if) can create this query and would appreciate
>> some insight.
>>
>> I am recording visits to a particular page at my web site using a table
>> (`visits`) with three columns.
>> Table 1 -`visits' (3 columns)
>> 1 - visits_id # auto_incr
>> 2 - ip_address
>> 3 - date_time
>>
>> When an ip_address (visitor) visits more than 25 times in less than half
>> an hour I INSERT that into another table 'over_limit', which has four
>> columns.
>>
>> Table 2 -`over_limit` (3 columns)
>> over_limit_id # auto_incr
>> ip_address
>> modified_date # timestamp = date this ip_address last exceeded limit
>>
>> (Note: if the same ip_address does it again (25 visits in under half an
>> hour), its entry is UPDATED.)
>>
>> Here's the query I don't know how to do. I want display the
>> `over_limit` ip_addresses that have been inserted or updated within the
>> last two weeks, but ordered by their last visit in `visits`. I know how
>> to select `'visits` entries that are less that two weeks old, but I do
>> not know how to order them as I'd like.
>>
>> Phrased in pseudo code, I want to
>>
>> (var $two_weeks_ago = timestamp of two weeks ago from right now)
>>
>> SELECT ip_address FROM 'over_limit` WHERE modified_date > $two_weeks_ago
>> ORDER BY the date_time of the latest visit (for the selected ip_address)
>> in table `visits`
>>
>> Any help would be appreciated.
>>
>
> SELECT a.foo, a.bar
> FROM table1 a JOIN table2 b on a.foo = b.baz
> WHERE ....
> ORDER BY b.quux
>
> Short answer: your order-by criteria don't have to be selected for
> output.

I knew that part (order by doesn't have to be selected). But now .......
I got it!

The correct SELECT query isn't simply a JOIN, or I might have gotten it
before now, but what you said at the end made me think to try something
new. I want to order by MAX(date_time). What I did not know is that I
*can* order using that expression, or actually the result of the
expression in an alias. If I add "MAX(visits.date_time) as max_date" to
the selection, I can then ORDER BY max_date.

(... When I tried simply to "ORDER BY MAX(visits.date_time)," I got an
error: "Invalid use of group function" I'm far from being a MySQL
expert, more of a hack, so I don't exactly know what that meant. But
when I added "MAX(visits.date_time) as max_date" into the selection, I
was then able to use "max_date" in the ORDER BY clause.)

Also, I did not want to have every row from `visits` that matched (as
the JOIN you suggested would do), just one row per ip_address, so I used
a GROUP BY clause as well.

Here is the final query that worked (note: I don't use table aliases
yet, .... trying to keep my queries easy to read and understand.)

SELECT over_limit.ip_address,
MAX(visits.date_time) as max_date
FROM `over_limit`
JOIN `visits` USING (ip_address)
WHERE over_limit.modified_date > '$two_weeks_ago'
AND visits.date_time > '$two_weeks_ago'
# need to limit both selections to the two week criteria
GROUP BY over_limit.ip_address
# so I only get one row per ip_address
ORDER BY max_date

I know that I may not have explained all of what I was trying to do very
well (it's even been confounding me, and I have all the details), so you
probably couldn't tell exactly what I was really after, but your efforts
certainly helped me get to a solution. Thank you!

The above query gives me every entry in the over-the-limit table for a
"visitor" (ip_address) who has gone over the limit in the last two
weeks, ordered by the last time they visited the page, whether they
exceeded the limit during that visit or not - per the join with table
`visits`). The key was being able to order by the derived max_date ...
and grouping by ip_address.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************