Errata

SQL Cookbook

Errata for SQL Cookbook

Submit your own errata for this product.

The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color key: Serious technical mistake Minor technical mistake Language or formatting error Typo Question Note Update

Version Location Description Submitted By Date submitted Date corrected
Mobi
Page xxii
top

"vendor-spe-cific solutions" should be "vendor-specific solutions"

Anonymous  Sep 26, 2013 
Mobi
Page 10
location 624 in mobi

"byrandom()" should be "by random()"

Anonymous  Sep 26, 2013 
Printed
Page 12
3rd paragraph

To improve alternative for COALESCE:
select case
when comm is not null then comm
else 0
end
from emp

In this way the order of the COALESCE is maintained

Note from the Author or Editor:
It's really the same thing, but the submitter is technically correct :-))

Go ahead and change it. The code occurs on page 11 of the copy I have, so change:

select case
when comm is null then 0
else comm
end
from emp

to

select case
when comm is not null then comm
else 0
end
from emp

Roy van Uffelen  Mar 11, 2012  Dec 14, 2012
Printed
Page 243
First paragraph under the MySQL section

The first paragraph under the MySQL section for "Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month" on page 243 says that "you can use simple arithmetic on the numeric values of weekdays (Sun - Sat is 0 - 6)..." It should be 1 - 7.

Note from the Author or Editor:
Correct, 0 should be changed to 1 and 6 should be changed to 7. Good catch!

Anonymous  Oct 27, 2010  Dec 14, 2012
Printed
Page 168
1st paragraph

... that you can sum two numbers by:

should be

... that you can multiply two numbers by:

Note from the Author or Editor:
Correct, it should be "multiply" not "sum".

What? No!  Aug 20, 2010  Dec 14, 2012
Printed
Page 543
Para 1, line 1

retuned s/be returned

Note from the Author or Editor:
Correct. It should be "returned" not "retuned".

Ed Oskiewicz  Jul 06, 2010  Dec 14, 2012
Printed
Page 529
Para 6

This account is confusing because a single symbol is used to denote "is a member of" and also "is not a member of".

Thus on the line below the one beginning "At this point" the "e" symbol should have a line through it and similarly in the two other occurrences on that page.

If my understanding is wrong then the text needs to be clarified to explain why the same symbol has two different and opposing meanings

Note from the Author or Editor:
This is a typo. Set membership uses the Greek lowercase epsilon. Non-membership uses the Greek lowercase epsilon with a line through it.

So, instead of (x ε x) it should be (x ~ε x)

but instead of using a tilde, please use a line through on epsilon (a strikethrough or whatever it's called).

Likewise,

(x ε y | x ε x) should be changed to (x ε y | x ~ε x)

and

(y ε y | y ε y) should be changed to (y ε y | y ~ε y)

Ed Oskiewicz  Jul 06, 2010  Dec 14, 2012
Printed
Page 128
third line from the top

Typo: "heirarchical" should be spelled "hierarchical" instead.

Note from the Author or Editor:
I don't see the word on page 128 of the printing I have. But if it's there, the submitter is correct. It should be "hierarchical".

Anonymous  Jun 01, 2010  Dec 14, 2012
Printed
Page 27
results table, in the middle of the page

In the ORDERED column, the value is supposed to be either the salary or the commission; however, employee MARTIN has the value 1300 in the ORDERED column, which is neither the salary (1250) nor the commission (1400). Two problems with this: First, where is this value coming from? And second, it affects the ordering, because MARTIN's commission (1400) is higher than MILLER's salary (1300) and therefore MARTIN should be listed after MILLER (and yet he's not). (This last problem with ordering is also on the preceding table, at the bottom of page 26.)

Note from the Author or Editor:
Looks like a copy/paste error, the submitter is correct. The result set on page 26 should be:

ENAME SAL JOB COMM
---------- ---------- --------- ----------
TURNER 1500 SALESMAN 0
ALLEN 1600 SALESMAN 300
WARD 1250 SALESMAN 500
SMITH 800 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
MARTIN 1250 SALESMAN 1400
CLARK 2450 MANAGER
BLAKE 2850 MANAGER
JONES 2975 MANAGER
SCOTT 3000 ANALYST
FORD 3000 ANALYST
KING 5000 PRESIDENT

and the result set on page 27 should be:

ENAME SAL JOB COMM ORDERED
---------- ---------- --------- ---------- ----------
TURNER 1500 SALESMAN 0 0
ALLEN 1600 SALESMAN 300 300
WARD 1250 SALESMAN 500 500
SMITH 800 CLERK 800
JAMES 950 CLERK 950
ADAMS 1100 CLERK 1100
MILLER 1300 CLERK 1300
MARTIN 1250 SALESMAN 1400 1400
CLARK 2450 MANAGER 2450
BLAKE 2850 MANAGER 2850
JONES 2975 MANAGER 2975
SCOTT 3000 ANALYST 3000
FORD 3000 ANALYST 3000
KING 5000 PRESIDENT 5000

Victor Ortega  May 28, 2010  Dec 14, 2012
Printed
Page 1
whole book

I assume the query results throughout this book were generated by querying an actual database with data in it.

Could the example data be made available for download so that readers can type in queries and explore variations while comparing their results with the book.

Note from the Author or Editor:
I have submitted the sample tables to O'Reilly, it should be available for download shortly.

Ed  Dec 06, 2008  Aug 12, 2009
Printed
Page 52
in the Discussion section on page 52 for the MySQL and PostgreSQL

In the Discussion section on page 52 for the MySQL and PostgreSQL
solution, the following sentence should be modified:

Old: "The following query is an alternative solution."

New: "The following query is an alternative solution; necessary if there
could be duplicate values in the column you are summing."

Anthony Molinaro
 
Aug 25, 2008  May 01, 2009
Printed
Page xxvii

6th paragraph, 2nd from bottom

sister-in-laws
-> sisters-in-law

Georgia and I
-> Georgia and me

*****************************

Anonymous    Feb 01, 2007
Printed
Page xxvi
Last paragraph

Missing Example Tables.
A.M. - Sending sample table scripts have been attached in this email

Note from the Author or Editor:

Customer service will be posting these ASAP

Anonymous    May 01, 2009
Printed, Other Digital Version
Page 2

Bullet on the bottom of page two reads:

* the COMM is NULL, or

which is a typo. it should read:

* the COMM is not NULL, or

Anonymous    Aug 01, 2008
Printed
Page 4

A.M. - COMM should be 1400 not 1300 for MARTIN (as is show in the preface on xxii).
A.M. - Pages where it is 1300 (and needs to be changed to 1400):

- page 4 in the solution section, the value for COMMISSION should be 1400 where it is 1300
- page 26 in the problem section, the value for COMM should be 1400 where it is 1300
- page 27 in the solution section, the value for COMM should be 1400 where it is 1300
- page 41 in the problem section, the valuee for COMM should be 1400 where it is 1300

Anonymous    May 01, 2009
Printed, Other Digital Version
Page 6

The table name is missing from line 2 of the MYSQL solution. it should read:

from emp

instead of just

from

Anonymous    Aug 01, 2008
Printed
Page 16
Problem section

In the problem section i mention the goal is to order by the last 2 chars, but in
fact i order by the last 3.
here's an example of the substring call put in a select:

select job, substr(job,length(job)-2) from emp order by 2

JOB SUBSTR(JO
--------- ---------
PRESIDENT ENT
CLERK ERK
CLERK ERK
CLERK ERK
CLERK ERK
MANAGER GER
MANAGER GER
MANAGER GER
SALESMAN MAN
SALESMAN MAN
SALESMAN MAN
SALESMAN MAN
ANALYST YST
ANALYST YST

this orders by the last 3 chars. to order by the last 2, you
would subtract the length of the job by 1, not 2:

select job, substr(job,length(job)-1) from emp order by 2

JOB SUBSTR(JO
--------- ---------
SALESMAN AN
SALESMAN AN
SALESMAN AN
SALESMAN AN
MANAGER ER
MANAGER ER
MANAGER ER
PRESIDENT NT
CLERK RK
CLERK RK
CLERK RK
CLERK RK
ANALYST ST
ANALYST ST

and to order by the last char, simply use the length of the job:

select job, substr(job,length(job)) from emp order by 2

JOB SUBSTR(JO
--------- ---------
CLERK K
CLERK K
CLERK K
CLERK K
SALESMAN N
SALESMAN N
SALESMAN N
SALESMAN N
MANAGER R
MANAGER R
MANAGER R
ANALYST T
PRESIDENT T
ANALYST T

Anonymous    Dec 14, 2012
Printed
Page 18
sql code on the bottom of the page

1 select data
2 from emp
3 order by replace(
4 translate(data,'0123456789','##########'),'#','')

NOW READS:

1 select data
2 from V
3 order by replace(
4 translate(data,'0123456789','##########'),'#','')

Anonymous    Jun 01, 2006
Printed
Page 26
Second table on the page, 7th Record, "Martin"

COMM value NOW READS 1400, not 1300.

Anonymous    Jun 01, 2006
Printed
Page 36
A.M. - Major cosmetic changes. Sending new version of pages 35.

A.M. - For page 36, please remove the following (located at the top of the page):

"As you can see, the condition DEPTNO NOT IN ( 10, 50,NULL) equates to:
not (deptno=10 or deptno=50 or deptno=null)
In the case where DEPTNO is 50, hereís how this expression plays out:
not (deptno=10 or deptno=50 or deptno=null)
(false or false or null)
(false or null)
null"

A.M. - The top of page 36 should begin with the sentence:

"In SQL, ìTRUE or NULLî is TRUE, but ìFALSE or NULLî is NULL!"

Anonymous    May 01, 2009
Printed
Page 41
Table'

5th record "Martin"
COMM value should be 1400, not 1300.

*****************************

Anonymous    Feb 01, 2007
Printed
Page 43

A.M. - in the MYSQL and SQL SERVER solution on page 43, the code part on line 21 reads:

and v.mgr = e.mgr

A.M. - but should instead read:

and coalesce(v.mgr,0) = coalesce(e.mgr,0)

A.M. - the same change should be made on page 44 for the code part on line 49

Anonymous    May 01, 2009
Printed
Page 49
second line in tip

The second sentence of the tip: "The recipe, , uses a Cartesian product..."
NOW READS: "The recipe 'Walking a String' uses a..."

Anonymous    Jun 01, 2006
Printed
Page 51 & 52
Solution for SQL Server doesn't work with SQL Server Express

This should be in the "errata" though it's technically microsoft's fault ;)

As a workaround, you can use the mysql solution, or you can do something
with a ranking function, like this:

select deptno,
sum(case when sal_rank=1 then sal end),
sum(total_bonus)
from (
select e.empno,
e.ename,
e.sal,
row_number()over(partition by e.empno order by e.sal) as sal_rank,
e.deptno,
e.sal*case when eb.type = 1 then .1
when eb.type = 2 then .2
else .3
end as total_bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
) tmp
group by deptno

Which will give the right results on both editions of SS 2005 (I just tested it).

Anonymous   
Printed
Page 58

A.M. - The sentence on the bottom of the page reads:

"Alternatively, union the results of the two different outer joins:"

A.M. - but it should read:

"Alternatively, since MySQL does not yet have a FULL OUTER JOIN, union the results of the two different outer joins:"

Anonymous    May 01, 2009
Printed
Page 62
Last paragraph

Section title is missing in the last sentence on the page:

"Check out Section in this chapter for details."

now reads

"Check out the section "Merging Records," later in this chapter, for details.

*****************************

Anonymous    Feb 01, 2007
Printed
Page 64
bottom

"insert into D (name) values ('Bar')"

NOW READS:
"insert into D (foo) values ('Bar')"

Anonymous    Jun 01, 2006
Printed
Page 73
A.M. - The DB2 and MYSQL solutions should be separate. The code listed will work for DB2, but needs

A.M. - a minor adjustment. In the solution section of DB2 and MYSQL on page 73, the code on line 4 reads:

4 where exists ( select null

A.M. - but it should read:

4 where exists ( select *

A.M. - The MySQL solution is the following:

MySQL

Include both EMP and NEW_SAL in the UPDATE clause of the UPDATE statement and join in the WHERE clause:

1 update emp e, new_sal ns
2 set e.sal=ns.sal,
3 e.comm=ns.sal/2
4 where e.deptno=ns.deptno

A.M. - The discussion must be modified on page 74. The DB2 discussion should no longer include MySQL
A.M. - and MySQL should be included in the PostgreSQL and SQL Server discussion.
A.M. - Since there are now 3 rdbms' mentioned in the discussion, the first sentence of the discussion
A.M. - for PostgeSQL, SQL Server, and (now) MySQL should be:

"The syntax is a bit different between these platforms, ..."

A.M. - the word "two" has been removed.

Anonymous    May 01, 2009
Printed
Page 80
A.M. - on page 80, the following should be added to the solution section (after what already there)

"
For MySQL users, you will need slightly different syntax because you cannot reference the same
table twice in a delete (as of the time of this writing):

delete from dupes
where id not in
(select min(id)
from (select id,name from dupes) tmp
group by name)

"

Anonymous    May 01, 2009
Printed
Page 87
DB2 Query

The query presented for DB2 to 'find the constraints and the columns they are on'
actually finds the contraintns and then assigns each constraint to every column of
the table. I haven't been able to find a query that produces the correct result in
DB2.

AUTHOR'S REPLY:

Here is the correct query for that recipe:

select a.tabname, a.constname, a.colname,
(select b.type from syscat.tabconst b
where a.tabname = b.tabname
and a.tabschema = b.tabschema
and a.constname = b.constname) as type
from syscat.keycoluse a
where a.tabname = 'EMP'
and a.tabschema = 'SMEAGOL'
UNION
select tabname, constname, colname, usage
from syscat.colchecks
where tabname = 'EMP'
and tabschema = 'SMEAGOL'

KEYCOLUSE shows all columns participating in primary,
Foreign, or unique keys.
COLCHECKS shows columns participating in check constraints.

Anonymous    Jul 01, 2007
Printed
Page 96

A.M. - On the top of page 96, the last sentence (in parenthesis) reads:

(SQL Server users will use SUBSTRING instead of SUBSTR):

A.M. - please change that to:

(SQL Server users will use SUBSTRING instead of SUBSTR and DATALENGTH instead of LENGTH):

Anonymous    May 01, 2009
Printed
Page 98
last paragraph

Text says that "a string literal comprising two quotes alone, with no intervening
characters, is NULL."

I have heard that this is an Oraclism only, that every other RDBMS considers it to be
merely an empty string. I have just confirmed it with PostgreSQL but don't have
access to the others.

AUTHOR'S RESPONSE:

The reader is right, but I did this purposely; my explanation follows:

In all the other rdbms's, an empty string is not null,
but i state the contrary in my book because in my opinion
it makes no sense that it isn't null.

Consider this, if you have a table with three columns,
one is a string, one is a number, the other is a date.
If you put no value in the number field, then it is null.
(remember, 0 is not null, 0 is a number).
if you put no value in the date field, it is null.

In other words, an empty date is null, an empty number is null,
so why should a string deviate from this?

What exactly is a string with no length anyway?
It's nothing, it should be null.

Let's say you have a table of contact info and you are storing first, middle,
and last name.
Someone with no middle name should have a null value as for their middle
name, but based on how non oracle db's do it, a person could have '' which is
not null. That always seemed weird to me. If it doesnt exist, it is null
(and a string with no length is nothing, it has no length, so why not have it be
null?).
Null is the value that represents "hey, nothing here".

In hindsight, I should have stated the facts, then included my rant in a sidebar.
So I apologize for any confusion this has caused.

Anonymous   
Printed
Page 122
In the MySQL solution section, second liine of the query

select deptno,
group_concat(ename order by empno separator, ',') as emps
from emp
group by deptno

NOW READS:
select deptno,
group_concat(ename order by empno separator ',') as emps
from emp
group by deptno

Anonymous    Jun 01, 2006
Printed
Page 128
A.M. - Please remove the trailing "x" on page 128 in the mysql solution located on code line 11 after

A.M. - the closing parenthesis

Anonymous    May 01, 2009
Printed
Page 128 & 129
In the descriptions for DB2, Oracle and SQL Server, there is an error

By walking the string passed to the IN-list, you can you can easily convert...
should be:
By walking the string passed to the IN-list, you can easily convert...

Anonymous    May 01, 2009
Printed
Page 170
top of the page, query given for mysql,postgresql, and sql server

The query doesn't give a running difference. It simply returns the first sal (ordered
by empno) then sums the rest and returns them as negative.

upon running it we see:

select a.empno, a.ename, a.sal,
(select case when a.empno = min(b.empno) then sum(b.sal)
else sum(-b.sal)
end
from emp b
where b.empno <= a.empno
and b.deptno = a.deptno ) as rnk
from emp a
where a.deptno = 10

EMPNO ENAME SAL RNK
---------- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING 5000 -7450
7934 MILLER 1300 -8750

to get the correct result set, use a view:

create view emp_vw as
select a.deptno, a.empno, a.ename,
case (select count(*)
from emp b
where b.empno <= a.empno
and b.deptno = a.deptno )
when 1 then a.sal else -a.sal end as sal
from emp a
where a.deptno = 10

and then simply do a running total:

select a.empno, a.ename, a.sal,
(select sum(b.sal)
from emp_vw b
where b.empno <= a.empno
and b.deptno = a.deptno ) as diff
from emp_vw a

EMPNO ENAME SAL DIFF
----- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING -5000 -2550
7934 MILLER -1300 -3850

My apologies. This was a silly mistake for an easy query.

Anonymous   
Printed
Page 192
last paragraph, second sentence

January 10th should be a Monday and January 11th should be a Tuesday. The sentence
as it exists, presents an impossible scenario:

"For example, if January 10th is a Tuesday and January 11th is a Monday, then the
number of working days between these two dates is two, . . ."

Anonymous    Jul 01, 2007
Printed
Page 232
First query (DB2)

When 1/31/2006 is substituted for current_date, the query determines the last day of January to be 1/28/2006.

This indeed an error. To fix the error you can use:

select
(date('2006-01-31') - day(date('2006-01-31')) day + 1 day)
firstday,
(date('2006-01-31')+1 month - day(date('2006-01-31')+1 month) day)
lastday
from t1

running it from db2 cmd line:

db2 => select
db2 (cont.) => (date('2006-01-31') - day(date('2006-01-31')) day + 1
day)
firstday,
db2 (cont.) => (date('2006-01-31')+1 month - day(date('2006-01-31')+1
month)
day) lastday
db2 (cont.) => from t1

FIRSTDAY LASTDAY
---------- ----------
01/01/2006 01/31/2006

1 record(s) selected.

The bug that crept into the original recipe is that it subtracted the numeric day value of the current date, when instead it
should use the numeric day value of the current date after 1 month has been added to it.

So, the new recipe that should be put in print in place of the current db2 recipe is this:

select
(date(current_date) - day(date(current_date)) day + 1 day)
firstday,
(date(current_date)+1 month - day(date(current_date)+1 month) day)
lastday
from t1

also, the discussion should be replaced with the following (for DB2 only ofcourse):

"To find the first day of the month simply find the numeric value of the current day of the month then subtract this from the
current date. For example, if the date is March 14th, the numeric day value is 14. Subtracting 14 days from March 14th gives
you the last day of the month in February. From there, simply add one day to get to the first of the current month. The
technique to get the last day of the month is similar to that of the first; substract the numeric day of the month from the
current date to get the last day of the prior month. Since we want the last day of the current month (not the last day of the
prior month), we need to add one month to the current date."

Anonymous    May 01, 2009
Printed
Page 233

A.M. - On page 233, for the SQL Server solution, the code on line 3 reads:

3 -day(getdate()),

A.M. - please change that to

3 -day(dateadd(month,1,getdate())),

A.M. - Since I am changing the code a bit, the explanation should be changed as well.
A.M. - In the paragraph explaining the solution the last sentence reads:

"
To get the last day of the month, add one month to the current date, and then substract
from that result the value returned by the DAY function applied to the current date plus 1 month,
again using the functions DAY and DATEADD:
"

A.M. - Additionally, a sentence must be changed on page 234 in the discussion section for the sql server
A.M. - solution. Please change the last sentence for the sql server discussion on page 234 to read:

"Add one month to the current date, then substract from it the value
returned by DAY(DATEADD(MONTH,1,GETDATE())) to get the last day of teh current month.
"

Anonymous    May 01, 2009
Printed
Page 367
Second query from the top of the page

select max(case when deptno=10 then empcount else null end) as deptno_10
max(case when deptno=20 then empcount else null end) as deptno_20,
max(case when deptno=10 then empcount else null end) as deptno_30
from (
select deptno, count(*) as empcount
from emp
group by deptno
) x

NOW READS:
select max(case when deptno=10 then empcount else null end) as deptno_10
max(case when deptno=20 then empcount else null end) as deptno_20,
max(case when deptno=30 then empcount else null end) as deptno_30
from (
select deptno, count(*) as empcount
from emp
group by deptno
) x

Anonymous    Jun 01, 2006
Printed
Page 372
sql on page 372 should be

select deptno dno, job,
max(case when deptno=10
then ename else null end) as d10,
max(case when deptno=20
then ename else null end) as d20,
max(case when deptno=30
then ename else null end) as d30,
max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as anals,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select deptno,
job,
ename,
row_number()over(partition by job order by ename) rn_job,
row_number()over(partition by deptno order by ename) rn_deptno
from emp
) x
group by deptno, job, rn_deptno, rn_job
order by 1

Anonymous    Jul 01, 2007
Printed
Page 419
A.M. - Please replace the first two sentences of the Discussion on page 419 to the following

"
If DEPTNO_SUBTOTALS is 0 and JOB_SUBTOTALS is 1 (in whichcase JOB is NULL), the value of SAL represents
a subtotal of salaries by DEPTNO created by CUBE. If JOB_SUBTOTALS is 0 and DEPTNO_SUBTOTALS is 1
(in whichcase DEPTNO is NULL) the value of SAL represents a subtotal of salaries by JOB created by CUBE.

"

Anonymous    May 01, 2009
Printed
Page 446
A.M. - the missing words are "PostgreSQL and MySQL".

A.M. - So, that sentence should read:

..., then you can solve theis problem using the techniques described for PostgreSQL and MySQL, ...

Anonymous    May 01, 2009
Printed
Page 448
code listing on the top of the page

with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select tree leaf___branch__root
from x

NOW READS:
with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select tree leaf___branch__root, depth
from x

Anonymous    Jun 01, 2006
Printed
Page 512
Solution section on page 512

From the author:
There is only one solution shown using the MODEL clause to create csv output from
rows. The goal was to show two solutions, one without iteration and one using
iteration (to highlight how much leaner code using MODEL can be if you iterate).

here's the alternative solution using MODEL:

select deptno, rtrim(ltrim(emps,','),',') emps
from (
select deptno,ename,idx,emps
from emp
model
partition by (deptno d)
dimension by (row_number()over(partition by deptno order by empno) idx)
measures (cast(null as varchar2(50)) emps, cast(null as varchar2(50)) tmp,
deptno,ename)
rules iterate (1000) until ename[iteration_number+1] is null
(
tmp[iteration_number+1] = ename[iteration_number+1],
emps[1] = emps[1] || ',' || tmp[iteration_number+1],
deptno[iteration_number] = deptno[1]
)
)
where idx = 1
order by 1

DEPTNO EMPS
------ --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

the solution on page 512 is 34 lines long, this one is half that.
that's the point of showing this, that in some cases using iteration can simplify
your sql.

Anonymous   
Printed
Page 542
A.M. - Please make the following sentence part of the current warning (the beartrap figure) on the top of page 542

"As of the time of this writing, SQL Server does not allow ORDER BY in the OVER clause
when used with aggregate window functions. SQL Server does permit ORDER BY in the OVER
clause when used with window ranking functions.

Anonymous    May 01, 2010