mySQL Query Help

[SkyHog]

I can't believe I can't wrap my brain around a way to do this.

I have a table with a series of position updates in it. Multiple people tracked simultaneously in the same table. Every update is timestamped, and there is a history of every position update kept in the same table.

The columns in the table are as such:

Lat (double), Long (double), PosID (int), UserId (ind), TimeStamp (TimeStamp)

PosID will always be unique, but everything else may be duplicated with different stuff, presumably, however, each user will never have duplicated TimeStamp.

I want to pull the most recent position for every UserId. Any ideas?

The best I can figure is how to pull the most recent position across the entire table, but I want the most recent per UserId.

Any help is appreciated. If it helps, here's some sample data:

Lat        Long             PosID             UserID               TimeStamp
35.233     -89.123          86                7                    1284523386
48.221     -64.218          85                12                   1284523381
48.220     -64.217          84                12                   1284523361
12.221     -2.218           83                9                    1284523162
12.221     -2.214           82                9                    1284523142
35.233     -89.120          81                7                    1284523111

So, what I want to return in my result set is:

Lat        Long             PosID             UserID               TimeStamp
35.233     -89.123          86                7                    1284523386
48.221     -64.218          85                12                   1284523381
12.221     -2.218           83                9                    1284523162

Thanks in advance!

 

[gprellwitz]

Okay, this is totally off the top of my head without having any recent knowledge of MySQL.

Do a join on a subselect where you get the
max(timestamp) group by userid

Something like
select * from mytable m1
join (select userid, max(timestamp) timestamp
from mytable m2
group by userid)
on m1.timestamp = m2.timestamp
and m1.userid = m2.userid

 

[gkainz]

select userid, max(pos_id), lat, long
from my_table
group by userid, lat, long

assuming that the maximum of pos_id within the group of individual userid groups is the latest record, and assuming that, timestamp is not required to get the last position per each individual.

off the top of my head - no db to test against right now and heading to bed

 

[jesse]

Here you go:

mysql> SELECT PosID,UserID,max(TimeStamp) as TimeStamp,Lat,`Long` FROM nick GROUP BY UserID;
+-------+--------+------------+--------+---------+
| PosID | UserID | TimeStamp | Lat | Long |
+-------+--------+------------+--------+---------+
| 86 | 7 | 2147483647 | 35.233 | -89.123 |
| 83 | 9 | 1284523162 | 12.221 | -2.218 |
| 85 | 12 | 1284523381 | 48.221 | -64.218 |
+-------+--------+------------+--------+---------+
3 rows in set (0.00 sec)

Based on this table:

CREATE TABLE `nick` (
`PosID` int(5) NOT NULL AUTO_INCREMENT,
`UserID` int(5) DEFAULT NULL,
`TimeStamp` int(10) DEFAULT NULL,
`Lat` float DEFAULT NULL,
`Long` float DEFAULT NULL,
PRIMARY KEY (`PosID`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;

Containing this test data:

INSERT INTO `nick` VALUES (86,7,1284523386,35.233,-89.123);
INSERT INTO `nick` VALUES (85,12,1284523381,48.221,-64.218);
INSERT INTO `nick` VALUES (84,12,1284523361,48.22,-64.217);
INSERT INTO `nick` VALUES (83,9,1284523162,12.221,-2.218);
INSERT INTO `nick` VALUES (82,9,1284523142,12.221,-2.214);
INSERT INTO `nick` VALUES (81,7,1284523111,35.233,-89.12);
INSERT INTO `nick` VALUES (87,7,2147483647,24,91);

You should use longitude instead of long. long is protected. you'll need to escape it with ``. Also think about using the MySQL DateTime column as it's easier to work with inside queries.

 

[gprellwitz]

select userid, max(pos_id), lat, long
from my_table
group by userid, lat, long

assuming that the maximum of pos_id within the group of individual userid groups is the latest record, and assuming that, timestamp is not required to get the last position per each individual.

off the top of my head - no db to test against right now and heading to bed

I think that will get you the most recent time they visited a particular location.

 

[JeffDG]

Sorry, don't know if this works in mySQL, most of my stuff is is M$SQL...

I'd do it with a join to a subquery...

Select
   pos.*
from
   Position pos
   inner join (
                   select UserID,TimeStamp=max(timestamp)
                   from position
                   group by UserID
                  ) ts on pos.userid=ts.userid and pos.timestamp=ts.timestamp

 

[CJones]

I had to do something similar yesterday, but Jesse beat me to the answer.

 

[SkyHog]

Here you go:
Based on this table:
Containing this test data:
You should use longitude instead of long. long is protected. you'll need to escape it with ``. Also think about using the MySQL DateTime column as it's easier to work with inside queries.

That did it. Thanks!

BTW, those aren't real column names. I have a table with about 35 columns, and I made up data for an example. The column's real name is "GPSLongitude"

Seriously, Jesse, thanks. I can't believe I couldn't grasp that one until you presented it.