Finding out of sequence records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding out of sequence records

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-03-08 : 06:38:12

Sometimes I have to import data into a sql table from an excel sheet. that table has an id and a seq field.

CREATE TABLE tbl (id int primary key, seq int)

Smaller ids should have smaller seq values. and sometimes this rule is violated. for the following sample data:

INSERT INTO tbl
SELECT 1, 870114 UNION ALL
SELECT 26, 870129 UNION ALL
SELECT 27, 871108 UNION ALL
SELECT 28, 870129 UNION ALL
SELECT 80, 870304 UNION ALL
SELECT 81, 871215 UNION ALL
SELECT 86, 870508 UNION ALL
SELECT 94, 870312 UNION ALL
SELECT 108, 870328 UNION ALL
SELECT 111, 870329 UNION ALL
SELECT 112, 870508 UNION ALL
SELECT 116, 870401 UNION ALL
SELECT 143, 870421 UNION ALL
SELECT 144, 870328 UNION ALL
SELECT 154, 870501 UNION ALL
SELECT 160, 870328 UNION ALL
SELECT 162, 870503 UNION ALL
SELECT 189, 870517 UNION ALL
SELECT 190, 870328 UNION ALL
SELECT 194, 870519 UNION ALL
SELECT 205, 870528 UNION ALL
SELECT 206, 870328 UNION ALL
SELECT 208, 870531 UNION ALL
SELECT 226, 870614 UNION ALL
SELECT 227, 870328 UNION ALL
SELECT 228, 870614 UNION ALL
SELECT 263, 870727 UNION ALL
SELECT 264, 871108 UNION ALL
SELECT 271, 870728 UNION ALL
SELECT 323, 870924 UNION ALL
SELECT 324, 871120 UNION ALL
SELECT 327, 871003 UNION ALL
SELECT 354, 871108 UNION ALL
SELECT 356, 871120 UNION ALL
SELECT 358, 871110 UNION ALL
SELECT 361, 871114 UNION ALL
SELECT 364, 871120

ids 27, 81, 86, 112, 144, 160, 190, 206, 227, 264, 324, 356, 358 are out of sequence.

and i want to find these id with their seq values. How can i get this result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 13:00:34

[code]
SELECT id,seq
FROM
(
SELECT t.id,t.seq,
(SELECT TOP 1 seq FROM tbl WHERE id>t.id ORDER BY id)AS NextSeq
FROM tbl t
)r
WHERE NextSeq<seq
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources