MYSQL Group Query Problems

[AndyY]

Hi guys I wonder if there are any MYSQL wizards who can help with a pain in the arse piece of code i'm trying to write.

My Database Looks like this

ID - Unique ID for each Records
VID - An ID Code relevent to each visitor (there can be multiple entries with this code)
TI - A Time Stamp for each Record
PID - A Text Code which can be on multiple records and on different VID's

And Example of what this DB will look like is below

1 1 1 P1
2 1 2 P3
3 1 3 P2
4 2 1 P2
5 3 1 P3
6 2 2 P1
7 2 3 P2
8 3 2 P3

Now what I want is an SQL statement that gaves me the last PID Code for each VID based upon TI.

So basically what each user was looking at before their session expired. But I actually want a count of the PID's

The table above should give me the results below

P1 - 0
P2 - 2
P3 - 1

As user 1 Ended on P2 and User 2 Ended on P2 and User 3 Ended on P3.

I've tried a number of Grouping combinations and the closest I can get is this..

SELECT MAX(table1.TI) AS ati,MAX(table1.ID) AS aid,table2.VID,COUNT(DISTINCT(table2.PID)) FROM test as table1,test as table2 WHERE table2.ID=aid GROUP BY table1.VID ORDER BY table1.ID;

This however says that aid is too ambigous and Errors out. My previous attempts at this just using groupings without trying to use 2 tables doesn't allocate the last values for PID even though using the MAX command I can get the correct ID. Sounds confusing hu, I'm not going to be defeated by this so any ideas would be great.

Regards

Andy

www.enjoy.co.uk

[Sxooter]

Well, it's nice to see someone using MySQL that has their select list elements either in the group by clause or being operated on by an aggregate function, so many people do things like:

select f1,f2 from table group by f1 and then wonder why they can't get the f2 value they want all the time...

Anyway the problem is that the query has to do the where portion first, then group by, THEN create the max(field) as aid.

So, you'll have to put max(field) in place of aid in the where clause. This might be one of those problems that is only solvable by using a subselect since the where clauses max(field) isn't operating on the more limited grouped by group, but on the whole data set.

Note that what you likely really want here is HAVING, where. Having fires after the group by portion of a query.