Using Ranking Functions to Deduplicate Data

// Input : A A B B C D D E
// Output: A B C D E

// Create a sample test data

declare @AlphaList table (AlphaKey char);

insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('C');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('E');

select AlphaKey from @AlphaList order by 1;

//Returns the output   : 1 A  2 A  3 B  4 B  5 C  6 D  7 D  8 E
select
ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;
//Returns the output   : 1 1 A  1 2 A   3 3 B   3 4 B  5 5 C  6 6 D  7 7 D  8 8 E
select
RANK() over (order by AlphaKey) as Rank, ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;

Usage of ISNUMERIC and how to create ISALLDIGITS functionality

/* Usage of ISNUMERIC */

/*
checks whether a number is Digits, Decimal, floating point, number, money
*/

// A point of summary

 SELECT [Ascii Code]        = STR(Number),
[Ascii Character]   = CHAR(Number),
[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
FROM Master.dbo.spt_ValuesWHERE Type = 'P'
AND Number BETWEEN 0 AND 255
AND ISNUMERIC(CHAR(Number)) = 1

//Results

SELECT ISNUMERIC(',') // returns 1 since the decimal contains ','
SELECT ISNUMERIC('$') // returns 1 since the money contain '$'
SELECT ISNUMERIC('€') // returns 1 since the money contain '€'
SELECT ISNUMERIC('£') // returns 1 since the money contain '£'
SELECT ISNUMERIC('¥') // returns 1 since the money contain '¥'

// so ISNUMERIC is not a permanent solution for checking ISALLDIGITS
// ISALLDIGITS Snippet

CREATE FUNCTION dbo.IsAllDigits/********************************************************************
Purpose:
This function will return a 1 if the string parameter contains only
numeric digits and will return a 0 in all other cases.  Use it in
a FROM clause along with CROSS APPLY when used against a table.

--Siva Shunmugam
********************************************************************/
(@MyString VARCHAR(8000))
RETURNS TABLE AS
RETURN (
SELECT CASE
WHEN @MyString NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END AS IsAllDigits

Sorting Months by Month Number.

/* Create a sample test record */

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

       DROP TABLE #MyHead





 SELECT TOP (1000000)

       SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),

       SomeAmount   = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)

  INTO #MyHead

  FROM master.sys.all_columns ac1

 CROSS JOIN master.sys.all_columns ac2

;



/* Add a clustered index to the table just to keep it from being a heap

    -- and to help with speed a bit by sorting the data.*/

CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime

    ON #MyHead (SomeDateTime)

;





/* This doesnt sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

;



/* The below sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

 ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)

;

Generating the top X rows from each group.

FYI: CROSS Apply works better than Filter in terms of performance

/* To display the records using Filter */
/* Create a temporary table */

CREATE TABLE #Runners
(
Runner integer NOT NULL,
Time integer NOT NULL,
Age integer NOT NULL
)


/* Insert sample records into the table*/

INSERT INTO #Runners

SELECT 1 , 10 , 20

UNION ALL

SELECT 2 , 15 , 20

UNION ALL

SELECT 3 , 11 , 20

UNION ALL

SELECT 4 , 12 , 30

UNION ALL

SELECT 5 , 18 , 30

UNION ALL

SELECT 6 , 9 , 40

UNION ALL

SELECT 7 , 16 , 40

UNION ALL

SELECT 8 , 13 , 30

UNION ALL

SELECT 9 , 13 , 30;

/* To retrieve the top X from each group using filter */

/* If both the time has the same time then the row number will be different for each same row */

with cteRunners
as
(
select * ,row_number() over (partition by Age order by Time ) as RowN
from #Runners
)

Select * from cteRunners
where RowN <=2
order by Age,Rown;


/* To give same row number for the same time present use DENSE_RANK */

with cteRunners 
as
( 
select *,DENSE_RANK() over (partition by Age order by Time ) as RowN 
from
#Runners
) 

Select  * from cteRunners 
where  RowN <=2 
order by Age,Rown 


/* To drop the temporary table */
DROP  TABLE #Runners/* Using CROSS APPLY */

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL drop table #RunnersBig

/* Create a table and enter the millions of sample data for checking the performance */
Create Table #RunnersBig
(
RunnerId integer identity ,
Time integer not null,
Age integer not null
)
go

insert into #runnersbig ( Time , Age )
select top 1000000 ABS ( checksum ( newid ()))% 1000 ,
ABS ( checksum ( newid ()))% 99
from sys . columns a cross join sys . columns b cross join sys . columns c
go
/* Create a Index */
create index idxrunnersbig on #runnersbig ( age , time ) include ( runnerid );

with cteN
as
(
select distinct Age number from #RunnersBig
)
Select *
from cteN cross apply ( Select top ( 2 ) * from #RunnersBig where #RunnersBig . Age = cteN . number order by Time ) as runners
order by cteN . number , runners . Time