www.webdeveloper.com
Results 1 to 6 of 6

Thread: Select TOP excluding rows based on prior values

  1. #1
    Join Date
    Jan 2008
    Location
    Surprise, AZ
    Posts
    119

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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,330
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jan 2008
    Location
    Surprise, AZ
    Posts
    119
    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.

  4. #4
    Join Date
    Jan 2008
    Location
    Surprise, AZ
    Posts
    119
    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:
    Code:
    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?

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,330
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Jan 2008
    Location
    Surprise, AZ
    Posts
    119

    resolved

    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:
    Code:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles