Help - Query out of order datetime within a table.

  • I have the following table with data.

    CREATE TABLE [dbo].[Table_1](

    [CreateDate] [datetime] NOT NULL,

    [rowid] [int] IDENTITY(1,1) NOT NULL

    ) ON [PRIMARY]

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-01 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-02 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-03 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-05 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-06 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-04 00:00:00.000')

    INSERT INTO [Test].[dbo].[Table_1]

    ([CreateDate])

    VALUES

    ('2011-01-07 00:00:00.000')

    When select * from Table_1, you will get the following result.

    CreateDate rowid

    2011-01-01 00:00:00.0001

    2011-01-02 00:00:00.0002

    2011-01-03 00:00:00.0003

    2011-01-05 00:00:00.0004

    2011-01-06 00:00:00.0005

    2011-01-04 00:00:00.0006

    2011-01-07 00:00:00.0007

    In my table, the CreateDate should be in chronological order but as you can see that '2011-01-04 00:00:00.000' is out of order.

    How can I write a select statement to identify this of out of order records in my table?

    Thanks!

  • How do you define 'out of order'? Lower date for higher identity value? Remember tables don't have a defined order.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you want rows back in a particular order , you HAVE TO specify an order by clause.

    Try this entry on my blog for more info...

    http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/sql-101-without-order-by-order-is-not-guaranteed.aspx



    Clear Sky SQL
    My Blog[/url]

  • The date column should be in ascending order similar like the rowid, however I have data that the date is not in ascending order and I would like to identify those records.

    Does anyone know how to write that query?

  • This is a performance hog, and if this is something you need to regularly run rather than as a cleanup item for the next few days, I'd recommend looking into the "Quirky Update"

    However, this will get you the result you need:

    SELECT distinct

    t1.*

    from

    #Table_1 AS t1

    JOIN

    #Table_1 AS t2

    ONt1.rowID > t2.RowID

    AND t1.CreateDate < t2.CreateDate

    Please note, it's a triangle join mess, so don't expect this to go quickly.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thank you so much!! You are the BEST!

  • This should do the same.

    Index on row_id and it should be OK, though not great performance.

    select *

    from Table_1 a

    inner join Table_1 b on a.rowid = b.rowid - 1

    where a.createDate > b.CreateDate

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/14/2011)


    This should do the same.

    Index on row_id and it should be OK, though not great performance.

    select *

    from Table_1 a

    inner join Table_1 b on a.rowid = b.rowid - 1

    where a.createDate > b.CreateDate

    You trust the contiguousness of the IDENTITY column more than I do, but you're right, that will run a lot faster if there are no gaps. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (1/14/2011)


    GilaMonster (1/14/2011)


    This should do the same.

    Index on row_id and it should be OK, though not great performance.

    select *

    from Table_1 a

    inner join Table_1 b on a.rowid = b.rowid - 1

    where a.createDate > b.CreateDate

    You trust the contiguousness of the IDENTITY column more than I do, but you're right, that will run a lot faster if there are no gaps. 🙂

    If there are gaps, a similar thing can be done just replacing the use of rowid with derived columns Row_number over order by rowid.

    Mine's worse performing on the low row counts, haven't tested on the larger, nor with indexes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply