Select TOP excluding rows based on prior values
I am having a hard time articulating my problem (as evidenced by the Title), but without including my entire query (which will not really help) here is the situation:
I have a dataset of GPS data that records numerous people every 5 seconds, so there is a lot of data. I want to include the TOP 50 speeds with a caveat:
- Row 1 should be the top speed in the system (simple simple);
- Row 2 should be the next highest speed (or same speed as Row 1) where the datetime is not within a 10 minute window of Row 1 if Row 2 is the same person as Row 1;
- Row 3 should use the same logic as Row 2, but applied to both Row 1 and 2 ...
I feel like this should be fairly simple, but short of writing 50 statements, I don't know how to do this.
I am happy to provide queries, datasets, or anything else, but it's the general "logic" that I'm struggling with. Thanks in advance.
I'm not an SQL super-hero, but I not seeing any obvious "simple" solution, either. Barring somebody cleverer coming up with an elegant solution, I'd probably just order by speed descending, with a relatively large limit clause, then iterate through the result set in the application code, keeping track of the last "good" entry saved in an array or object, and comparing subsequent results against it for your user/timestamp requirements, until you get 50 results.
Dang. I was really hoping that I was just looking past the simple solution ... happens more often than I'd like to admit. I guess I'll keep running at the wall hoping it will give up first. Thank you for your insight.
Sooooo ... I started down a path here, and I feel like there is a simple solution looking me in the face, but I'm missing it:
The only problem is, INSERT INTO when used in conjunction with SELECT inserts all rows at once, therefore the WHERE NOT EXISTS is always true ... is there a way to do this one row at a time?
INSERT INTO #topspeed
select TOP 50
from surround a
WHERE NOT EXISTS (SELECT
FROM #topspeed t
WHERE a.unid = t.unid
AND a.realtime BETWEEN DATEADD(MI, -10, t.realtime)
AND DATEADD(MI, 10, t.realtime)
AND a.back1 <> 0
AND a.back2 <> 0
order by a.speed DESC
Maybe add a LIMIT 1 to the sub-query?
Which has me thinking it could all go into a stored function, and let the function do a loop to populate the temporary table, then return its recordset when finished? It's still 50 iterations, but at least it's all running in a (pre-compiled) function on the DB server.
Thank you for helping me throughout this process. I got it figured out (I'm guessing a procedure is the way to go, but knowing how our DBAs are a bit protective of the database I'm doing it on the server side.) I ended up doing something similar to my previously submitted code, but just as part of a select:
SELECT TOP 50
FROM surround s
WHERE NOT EXISTS (SELECT
FROM surround t
WHERE t.unid = s.unid
AND t.realtime BETWEEN DATEADD(MI, -10, s.realtime)
AND DATEADD(MI, 10, s.realtime)
AND t.srowno < s.srowno
ORDER BY s.avgspeed DESC
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)