Interesting SQL Propblem 1

[VVVA]

I've got a sql question. If I have a table that has two columns, book and page, what is the best way to get a count of all the missing pages for each book?

So, if I have this table...

Book   Page

0001   0001
0001   0002
0001   0005
0001   0006
0002   0001
0002   0005
0002   0006
0002   0009
0003   0001
0003   0002
0003   0003

I want these results....

Book   MissingPages
0001   2
0002   5
0003   0

I've managed to do this, but my code is slow, cumbersome, and I'd be better off using a cursor. Anybody have an idea of how to do this fast and elegantly?

 

[SQLDenis]

max - count will give you the missing pages
group by book of course

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[VVVA]

OMG! Thanks a million SQLDennis...*beats head against desk*

 

[Olaf Doschke]

...which assumes the last page(s) of each book is/are not missing.

But maybe that is not the real world problem.

Bye, Olaf.

 

[AlexCuse]

Isn't the problem unsolvable if you don't have the last page?

You need that or more information (and if you had more information this would not be a problem at all)

Ignorance of certain subjects is a great part of wisdom