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.
| Author |
Topic |
|
golden_manish
Starting Member
13 Posts |
 Posted - 2003-09-14 : 11:23:31
|
| Hello Friends, I am manish from India.I have a small question, for you.How can i find out, if there are any missing records in a table.Ex. There is a Table with 10 Records.Records - (Primary Key) 1,2,3,4,5,6,8,9 and 10 are in the table.How can i find out, the fastet way that Record No 7 is missing.Thanks friends. |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-09-14 : 12:50:54
|
| A good way to do this is build a temp table of numbers containing your min and max id's and do an outer join of this numbers table with your table. Null rows in your table indicate a gap at certain numbers. If you need more information, please ask.Sarah Berger MCSD |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-14 : 17:27:30
|
| Take a look at this one, too: SELECT x.Value as GapStart, Max(y.value) AS GapEndFROM Vals yINNER JOIN Vals x ON y.Value < x.valueGROUP BY x.ValueHAVING Max(y.Value) <> x.value - 1- Jeff |
|
|
|
|
|
|
|
|
Record ID