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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s