Stairway to Database Design Level 8: Cursors

DECLARE @MedianValue as decimal
DECLARE @rowCount as int

SELECT @rowCount = COUNT(*) FROM dataTable

SELECT
@MedianValue = dataValue
FROM
(
SELECT
dataValue
,ROW_NUMBER() OVER (ORDER BY dataValue) as rowNumber
FROM dataTable
) as t
WHERE
t.rowNumber = @rowCount / 2 + 1

DECLARE @MedianValue as decimal
DECLARE @rowCount as int

SELECT @rowCount = COUNT(*) FROM dataTable

SELECT
dataValue as Median
,groupingValue
FROM
( -- count of values partitioned by grouping Value
SELECT
count(dataValue) / 2 as medianLocation
,groupingValue
FROM dataTable
GROUP BY groupingValue
) as countTable
INNER JOIN
( -- row counts table partitioned by grouping Value
SELECT
dataValue
,ROW_NUMBER() OVER (ORDER BY dataValue PARTITION BY groupingValue) as rowNumber
,groupingValue
FROM dataTable
) as rowTable
ON countTable.medianLocation = rowTable.rowNumber
AND countTable.groupingValue = rowTable.groupingValue

david.howell (11/2/2011)

It's quite disrespectful calling Mr Celko silly.

"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."

This is a correct statement, but probably just needs a comma after "electronics". In electronics yes its about power maximisation and avoiding reflections, but in computing it is certainly about the mismatch between relational and procedural ( which are themselves analogues for parallel and serial).

On that Dinosaur languages comment, a quick search on wikipedia reveals
"Lisp is the second-oldest high-level programming language in widespread use today; only Fortran is older (by one year)." Maybe it should be LISP.

Joe Celko is a venerable statesman in the SQL community, he contributed to the SQL-89 and SQL-92 standards and has written and contributed to more than 8 books, not to mention this informative Stairway series. Attacking him on trite points and calling him silly just detracts from the point of all this.

Newbies and Seasoned pros alike can benefit from stairway installments. They suit my short attention span perfectly.

The comment about procedural languages providing an undercover cursor was something I'd never considered, but I suddenly recall all the csv / xls and other flat reports i've ever done that benefitted dramatically from not processing the dataset in the procedural language, and shifting the bulk work back to SQL Server.

Where is Jeff Moden's comment in all this? If you still don't believe cursors are slow just ask him!