Results 1 to 8 of 8

Thread: SQL question 2

  1. #1
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Unanswered: SQL question 2

    GOAL: To find three max time differents in the same column. (1 is compared with, 2 with 3, 3 with 4,...)

    Example:

    ID TIME:
    1 12:00:00
    2 12:01:00
    3 12:02:00
    4 12:05:00
    5 12:15:00
    6 12:35:00

    ID Result:
    1 20 min (or 0,333)
    2 10 min
    3 3 min


    I did that using VBA, but it takes to much time. I am wondering if this is somehow possible in SQL?

    Best regards.

    Martin
    Back to the basics...

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I wish I had time to noodle on this one today.

    You may want to post this in the SQL forum also.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    martin, please explain how you got 20 minutes from this comparison --

    1 12:00:00
    2 12:01:00
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    He didn't, The diff of 20 is between item 5 and 6 . I think you will have to use VB for this but, I am not the SQL expert here.
    Darasen

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha

    Martin, what is the datatype of the TIME column?

    and are you matching "next" rows in a sequential fashion, or will it always be via the ID value, i.e. t1.ID + 1 = T2.ID?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Sql

    maybe I wasn't accurate enough

    I have table with few hundred thousand records (2k to 10k per day). Daily, I wanna get the first 3 maximum time diff betwen ascending sorted time values. So, yes I am comparing only in sequential fashion. the diff between first record and the last has no meaning for my analysis. ID value does not provide any usufull information. I just put IDs there for visual outlook (sorry).

    As I understand SQL is more or less record oriented. So, any filteres or calculation you put in it, are based on the records values. But here I am trying to compare/calculate two sequential records.

    Any link for a tutorial/example/case study are very welcomed!

    Best regards.

    Martin
    Back to the basics...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mp218
    Daily, I wanna get the first 3 maximum time diff betwen ascending sorted time values. So, yes I am comparing only in sequential fashion.
    you want to compare a few hundred thousand rows to a few hundred thousand rows for a total of several trillion comparisons on a daily basis

    because if they are being compared sequentially, each row has to be compared to all the other rows in a self join

    yes, you can do this with sql, but it's going to be, um, how shall i put this, a bit slow

    my recommendation is that you sort the rows and read them with an application language script like, um, i dunno, VBA or something, which is going to be a lot faster

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2005
    Posts
    1

    Talking Insert Trigger

    I think Insert Time is the key to this.

    When a new record is loaded, a piece of information missing from the row itself is the time difference between it and the former last record. If you had an Insert trigger to lookup the timestamp on the former last record, compute the difference between it and this one, and then store it in the current record, then the act of finding the top 3 largest differences would be a simple

    SELECT TOP 3 TIME_DIFF FROM MY_TABLE ORDER BY TIME_DIFF DESC

    In the insert trigger, one could just add a SELECT MAX(Date_Column) to get the timestamp of the previous entry.

Posting Permissions

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