GROUP BY `pain` HAVING 'headache' = TRUE

[0ddball]

Good evening, all. I'm trying to re-invent the wheel again purly in the interest of something to do that looks like work and makes me more employable else where.

I'm making a hit-counter that can track exit-points. It's going to do this by spotting the page on which the user was last seen.

There are a few rules:

1 - I can't use more than one SQL query... Stored procedures are right out... it's going to get messy.

2 - Users must have been inactive for more than two hours.

3 - MySQL 4.1 only! I can't afford MySQL 5.0 from my ISP yet!

My table:

+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| hitId       | int(10) unsigned     |      | PRI | NULL    | auto_increment |
| browserId   | int(10) unsigned     |      | MUL | 0       |                |
| languagesId | int(10) unsigned     |      | MUL | 0       |                |
| res_x       | smallint(5) unsigned | YES  |     | NULL    |                |
| res_y       | smallint(5) unsigned | YES  |     | NULL    |                |
| cDepth      | tinyint(3) unsigned  | YES  |     | NULL    |                |
| time        | datetime             | YES  |     | NULL    |                |
| referer     | varchar(200)         | YES  |     | NULL    |                |
| hitUrl      | varchar(150)         | YES  |     | NULL    |                |
| ip_num      | int(10) unsigned     | YES  |     | NULL    |                |
| username    | varchar(255)         | YES  |     | NULL    |                |
| trackId     | varchar(100)         | YES  | MUL | NULL    |                |
+-------------+----------------------+------+-----+---------+----------------+

My attempt so far:

SELECT
	hitUrl,
	COUNT(*) AS count
FROM
	page_hits
WHERE
	hitId IN
	(
		SELECT
			hitId
		FROM
			page_hits
		GROUP BY
			trackId
		HAVING
			`time` = GREATEST(`time`)
		)
GROUP BY
	hitUrl

As I'm sure many of you can see - it isn't working. I havn't even begun to figure out the two hour limit. I want a separate row for each trackId you see, and each trackId has a different MAX(`time`)

eBeer for the first person to help me! (or eWine, eJ2O, eWater, eTc, if you're not a beer person)


Yet another unchecked rambling brought to you by:
Oddball

 

[0ddball]

I tend not to do this - but I'm going to have to. Bump

Does anyone have any ideas on this - if you couldn't dig the question out of all my rubbish - or you don't know - please post and say so!

I feel like I've asked a quesion so stupid no one wants to talk to me.


Yet another unchecked rambling brought to you by:
Oddball

 

[0ddball]

Ok - seeing as how no one else has come up with an answer, and I've finaly worked it out after two days, added to my massive hatred for people who just post things like "Don't worry - I've fixed it!" and never tell us how...

Here's the answer I came up with:

SELECT
	`hitUrl`, 
	COUNT(*) AS `count`
FROM
	`page_hits` AS `b`
WHERE
	`b`.`time` = (
		SELECT 
			MAX(`time`)
		FROM
			`page_hits` AS `c`
		WHERE
			`b`.`trackId` = `c`.`trackId`
		)
	AND
		`b`.`time` > SUBTIME(CURRENT_TIMESTAMP, '2:0:0')
GROUP BY
	`b`.`hitUrl`

The revelation coming when I realised that I could use tables across Subquery boundaries. Apparently they're called Correlated Subqueries. Now - like any good poor programmer I'm going to go away and abuse this new technique, using it for everything I come up with - including making dinner.


Yet another unchecked rambling brought to you by:
Oddball