0

I have a SQL question. I am not very strong with using SQL and thought that someone here could give me a hand. Any help would be appreciated. Thanks in advance.

I have a SQL table that consists of the following:

id INT,
lat VARCHAR(255),
lon VARCHAR(255),
timestamp TIMESTAMP

This table consists of several gps locations from multiple different ID's. The Primary key is the ID and timestamp.

What I need to do is to select the latest 3 sets of coordinates from each unique id. What would be the best way to accomplish this?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
jwebster
  • 628
  • 2
  • 10
  • 20
  • possible duplicate of [How to SELECT the newest four items per category?](http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category) – Bill Karwin Apr 01 '14 at 22:53
  • I wish the duplicate thingy was a bit smarter – Strawberry Apr 01 '14 at 22:59
  • @BillKarwin I am trying to run through the answers from the other post. The problem I am having is A)Like I mentioned, I am not very well-versed in SQL, and B)I am trying to do this for one table. So, I don't really need the joins. That does appear to be close to what I am trying to do however. I am just trying to figure it out at the moment. – jwebster Apr 01 '14 at 23:01
  • @jwebster, follow the [tag:greatest-n-per-group] tag. There are many answers to that general type of problem. Granted, 9 out of 10 are solving the greatest *1* per group, which is solved differently. But some of the solutions are for your case, greatest N per group. – Bill Karwin Apr 01 '14 at 23:13

1 Answers1

0

Okay after quite a few edits, the below should definitely work. I tested it and it was clean.

SELECT t.id, t.lat, t.lon, t.timestamp
FROM table t
LEFT OUTER JOIN table t2
  ON (t.id = t2.id AND t.timestamp < t2.timestamp)
GROUP BY t.id, t.lat, t.lon, t.timestamp
HAVING COUNT(*) < 3
ORDER BY t.id, t.timestamp desc;

Note, I find it super frustrating that both Oracle and MySQL still don't support an order by/limits in subqueries! Would have made this much simpler the first time around...

SS781
  • 2,507
  • 2
  • 14
  • 17
  • Any results on trying to run this? – SS781 Apr 02 '14 at 01:14
  • I have tried to run that just now, but I will apparently have to change the version of MySQL that I am currently using in order to use it. I am getting the following error: Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' I truly do appreciate the help and the attempt at it. This one is proving to be a bit trickier of a problem than I expected it to be. – jwebster Apr 02 '14 at 03:49
  • which version are you using? it looks like 5+ supports it: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html – SS781 Apr 02 '14 at 03:53
  • I believe it should definitely work with the answer above, so long as the MySQL version supports the use of any, all, etc. for subqueries. – SS781 Apr 02 '14 at 03:57
  • Running the SELECT @@Version returns that I am running '5.5.34-cll-lve' – jwebster Apr 02 '14 at 04:24
  • And running that from within MySQL Workbench and/or PHPMyAdmin both returns the same error of this version not supporting it. So I will just have to either find another way, or change versions of SQL. – jwebster Apr 02 '14 at 04:29
  • I made a slight change that does not require 'any'. Let me know if this new query works. – SS781 Apr 02 '14 at 17:21
  • Any update on this? I am very interested to see if this works actually, since it would be the skeleton of a good generic query. – SS781 Apr 03 '14 at 18:32