Monthly Archives: April 2015

Distinct Row Count Table

T-SQL, , , , , ,

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