Monthly Archives: April 2015

Distinct Row Count Table

If you come from a programming background you might want to do a loop using TSQL rather than using a cursor.

The syntax for this is pretty recognisable, exempli gratia,

DECLARE @thisThing INT
DECLARE @thatThing INT
SET @thisThing = 1
SET @thatThing = 10
WHILE @thisThing < @thatThing
BEGIN
Do stuff
SELECT GETDATE()
SET @thisThing = @thisThing + 1
END

view raw
gistfile1.sql
hosted with ❤ by GitHub

However if you want to feed in dynamic variables captured from a table you can create a distinctive row count table.

This deduplicates the occurrence of a field value and assigns it a row number.

Where this can be used will be included in a more complicated example coming in the future.

IF OBJECT_ID('tempdb..#rowCountTB') IS NOT NULL
DROP TABLE #rowCountTB;
WITH DistinctTable
AS (
SELECT DISTINCT [fieldValue]
FROM [tableName]
)
,RowNumber
AS (
SELECT ROW_NUMBER() OVER (
ORDER BY [fieldValue]
) AS RowNum
,[fieldValue]
FROM DistinctTable
)
SELECT *
INTO #rowCountTB
FROM RowNumber
ORDER BY [fieldValue]

view raw
gistfile1.sql
hosted with ❤ by GitHub