January 13, 2011 at 5:52 pm
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.000 1
2011-01-02 00:00:00.000 2
2011-01-03 00:00:00.000 3
2011-01-05 00:00:00.000 4
2011-01-06 00:00:00.000 5
2011-01-04 00:00:00.000 6
2011-01-07 00:00:00.000 7
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!
January 14, 2011 at 12:43 am
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
January 14, 2011 at 2:02 am
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...
January 14, 2011 at 10:35 am
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?
January 14, 2011 at 11:33 am
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
ON t1.rowID > t2.RowID
AND t1.CreateDate < t2.CreateDate
Please note, it's a triangle join mess, so don't expect this to go quickly.
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
January 14, 2011 at 12:28 pm
Thank you so much!! You are the BEST!
January 14, 2011 at 12:48 pm
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
January 14, 2011 at 12:52 pm
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. 🙂
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
January 14, 2011 at 1:04 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply