[RESOLVED] Last Row in GROUP BY problem

joe_C_nice

I have this tricky query that invloves GROUP BY.

Here's the query

 


mysql_query("select table1.session, table1.location, table1.ip, MAX(table1.time) as S, table2.user_id from track_session AS table1 INNER JOIN session_var AS table2 ON table1.session=table2.session group by table1.session order by S desc ");

The problem i'm having is that table1.location returns the first inserted row for each grouped table1.session.
What I need is the last inserted table1.location, so i'm able to display each users last know location.

Here are my table structure.

The track_session table has four colums:
session | ip | location | time

The session_var table has two columns:
session | user_id

The location column stores a string so i'm not able to use the aggregate function MAX.

Any help would be greatly appreciated.

Lars_Berg

What I need is the last inserted table1.location, so i'm able to display each users last know location.

There is no way to get the last inserted row. The DBMS does not have that type of information.
What you can get is the row with the highest time value which may (or may not) be the same as the last inserted row

select table1.session, 
table1.location, 
table1.ip, 
table1.time as s, 
table2.user_id 
from track_session AS tabe1l 
INNER JOIN session_var AS table2 ON table1.session=table2.session 
where table1.time in (select  max(time) 
from track_session
where location = table1.location)
order by s desc

Also, when doing a group by query all non-aggregated columns in the select list should be included in the group by clause.

joe_C_nice

The MySQL version i'm using doesn't allow where table1.time in (select max(time) statements.

Are there any other suggestions.

Roger_Ramjet

If you can't use subqueries then you need to use 2 queries. Do a create table .. select ..
to get the last time value for each user in a new table, and then use the new table to get the location. Let me know if you need help writing those queries.

joe_C_nice

Roger Ramjet YHPM

Lars_Berg

The query can be rewritten without subselect, albeit less intuitive and less understandable

select table1.session, 
table1.location, 
table1.ip, 
table1.time as s, 
table2.user_id 
from track_session AS table1 
INNER JOIN session_var AS table2 ON table1.session=table2.session 
INNER JOIN track_session as table3 on table2.session = table3.session
group by table1.session, 
table1.location, 
table1.ip, 
table1.time as s, 
table2.user_id 
having table1.time = max(table3.time)
order by s desc

joe_C_nice

Thank you Lars Berg.