Select TOP excluding rows based on prior values

This site is best viewed in a modern browser with JavaScript enabled.

Select TOP excluding rows based on prior values

gift2women

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 ...
Etc.

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.

NogDog

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.

gift2women

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.

gift2women

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:

INSERT INTO #topspeed
     select TOP 50
            *
       from surround a
      WHERE NOT EXISTS (SELECT
                               1
                          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

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?

NogDog

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.

gift2women

NogDog,

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
             s.*
        FROM surround s
       WHERE NOT EXISTS (SELECT
                                1
                           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