Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: how to select specific rows..

    Hi Friends...

    I have one table , suppose TableA which contains following columns..

    I want to select records from this table where time difference is more than 2 min. (tec_insert_date) This means first record time and second one, then second one and third one..and so on..

    How to select these records...pls give me some sql query...

    thanks in advance


    tec_number tec_insert_date tec_description
    ----------- ------------------------------------------------------ --------113909 2005-12-19 18:59:01.920 Description
    113910 2005-12-19 18:59:02.043 Description
    113911 2005-12-19 18:59:02.060 Description
    113912 2005-12-19 18:59:02.060 Description
    113913 2005-12-19 18:59:02.090 Description
    113914 2005-12-19 18:59:02.090 Description
    113915 2005-12-19 18:59:02.263 Description
    113916 2005-12-19 18:59:02.437 Description
    113917 2005-12-19 19:00:34.703 Description
    113918 2005-12-19 19:00:34.720 Description
    113919 2005-12-19 19:00:34.937 Description
    113920 2005-12-19 19:00:35.607 Description
    113921 2005-12-19 19:00:35.607 Description
    113922 2005-12-19 19:00:35.840 Description
    113923 2005-12-19 19:00:35.903 Description
    113924 2005-12-19 19:00:36.200 Description
    113925 2005-12-19 19:00:36.200 Description
    113926 2005-12-19 19:00:36.217 Description
    113927 2005-12-19 19:00:36.217 Description
    113928 2005-12-19 19:00:36.217 Description
    113929 2005-12-19 19:00:36.230 Description
    113930 2005-12-19 19:00:36.230 Description
    113931 2005-12-19 19:00:36.230 Description
    113932 2005-12-19 19:00:36.230 Description
    113933 2005-12-19 19:00:36.323 Description
    113934 2005-12-19 19:00:36.450 Description
    113935 2005-12-19 19:00:38.863 Description
    113936 2005-12-19 19:00:38.943 Description
    113937 2005-12-19 19:00:38.943 Description
    113938 2005-12-19 19:00:39.100 Description
    113939 2005-12-19 19:00:39.147 Description
    113940 2005-12-19 19:00:39.300 Description
    113941 2005-12-19 19:00:40.920 Description
    113942 2005-12-19 19:01:23.363 Description
    113943 2005-12-19 19:03:03.533 Description
    113944 2005-12-19 19:03:08.197 Description
    113945 2005-12-19 19:03:12.853 Description
    113946 2005-12-19 19:03:14.087 Description
    113947 2005-12-19 19:04:40.730 Description
    113948 2005-12-19 19:04:44.843 Description
    113949 2005-12-19 19:04:51.030 Description
    113950 2005-12-19 19:04:56.313 Description
    113951 2005-12-19 19:04:58.950 Description
    113952 2005-12-19 19:09:19.950 Description
    113953 2005-12-19 19:09:21.277 Description
    113954 2005-12-19 19:09:21.340 Description
    113955 2005-12-19 19:09:24.397 Description
    113956 2005-12-19 19:09:35.660 Description
    113957 2005-12-19 19:09:46.480 Description
    113958 2005-12-19 19:09:47.790 Description
    113959 2005-12-19 19:10:23.713 Description
    113960 2005-12-19 19:10:23.980 Description
    113961 2005-12-19 19:11:02.307 Description
    113962 2005-12-19 19:11:52.040 Description
    113963 2005-12-19 19:12:31.800 Description
    113964 2005-12-19 19:12:31.940 Description
    113965 2005-12-19 19:12:31.940 Description
    113966 2005-12-19 19:13:10.500 Description
    113967 2005-12-19 19:13:10.500 Description
    113968 2005-12-19 19:13:50.697 Description
    113969 2005-12-19 19:13:50.697 Description
    113970 2005-12-19 19:13:50.743 Description
    113971 2005-12-19 19:13:50.870 Description
    113972 2005-12-19 19:13:50.883 Description
    113973 2005-12-19 19:14:09.290 Description
    113974 2005-12-19 19:14:47.680 Description
    113975 2005-12-19 19:14:47.693 Description
    113976 2005-12-19 19:14:48.630 Description
    113977 2005-12-19 19:14:48.630 Description
    113978 2005-12-19 19:14:49.127 Description
    113979 2005-12-19 19:14:49.127 Description
    113980 2005-12-19 19:14:49.160 Description
    113981 2005-12-19 19:14:49.300 Description
    113982 2005-12-19 19:14:49.313 Description
    113983 2005-12-19 19:15:07.287 Description
    113984 2005-12-19 19:15:07.783 Description
    113985 2005-12-19 19:21:43.093 Description
    113986 2005-12-19 19:22:01.920 Description
    113987 2005-12-19 19:22:01.920 Description
    113988 2005-12-19 19:22:20.780 Description
    113989 2005-12-19 19:22:38.547 Description
    113990 2005-12-19 19:22:38.547 Description
    113991 2005-12-19 19:22:39.340 Description
    113992 2005-12-19 19:22:39.340 Description
    113993 2005-12-19 19:22:40.263 Description
    113994 2005-12-19 19:22:41.010 Description
    113995 2005-12-19 19:22:41.867 Description

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Code:
    select
      a.tec_number ,
      a.tec_insert_date ,
      a.tec_description ,
      b.tec_insert_date
    from
      dbo.mytable a inner join dbo.mytable b on
          a.tec_number = b.tec_number + 1  -- or -1, i'm not sure
    where
      datediff(mi, a.tec_insert_date, b.tec_insert_date) > 2
    Note that this depends on tec_number being sequential with no missing entries (ie, a skipped entry may lead to invalid results).

    The usual caveats about my code: it's usually ugly and slow.

    Your mileage may vary, blindman will be along shortly to post the correct solution!

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is what I would do, unless you have gaps in your auto-sequence. If you do, then you will need a more complex subquery to determine the prior record for each record. And the resulting SQL would be even uglier and slower.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2005
    Posts
    4

    tec_number is in seqence always

    tec_number is in seqence always...Thanks

Posting Permissions

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