T-SQL RANK() , DENSE_RANK() , NTILE(), ROW_NUMBER()

26/01/2010 Leave a comment

Rank():
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Usage: RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Dense_Rank() :
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Usage: DENSE_RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Ntile():
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Usage: NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Row_Number():
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )  

Example  

All Recordset :   

select * from finals 
RecordID Name Surname Course Point
1 Bugra Postaci Mathematics 89
2 Bugra Postaci Lecture 90
3 Dany Lowe Mathematics 75
4 Dany Lowe Lecture 85
5 Alice Marcel Mathematics 77
6 Alice Marcel Lecture 100
7 Simon Duru Mathematics 45
8 Simon Duru Lecture 58

 

select row_number() over(order by RecordID desc) as RowNumber ,* from finals 

RowNumber RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 7 Simon Duru Mathematics 45
3 6 Alice Marcel Lecture 100
4 5 Alice Marcel Mathematics 77
5 4 Dany Lowe Lecture 85
6 3 Dany Lowe Mathematics 75
7 2 Bugra Postaci Lecture 90
8 1 Bugra Postaci Mathematics 89

 

select ntile(2) over(order by RecordID desc) as [Ntile] ,* from finals 

Ntile RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
1 7 Simon Duru Mathematics 45
1 6 Alice Marcel Lecture 100
1 5 Alice Marcel Mathematics 77
2 4 Dany Lowe Lecture 85
2 3 Dany Lowe Mathematics 75
2 2 Bugra Postaci Lecture 90
2 1 Bugra Postaci Mathematics 89

 

as you see ntile function just divide the scope with given number 

select rank() over (order by Surname) as [Rank], * from finals 

Rank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
3 3 Dany Lowe Mathematics 75
3 4 Dany Lowe Lecture 85
5 5 Alice Marcel Mathematics 77
5 6 Alice Marcel Lecture 100
7 1 Bugra Postaci Mathematics 89
7 2 Bugra Postaci Lecture 90

 
rank function is working like  match one “Duru” as 1 match another “Duru” as 1 but total count as 2 , next surname matches “Lowe” as count +1 as 3 , another “Lowe” as 3  ; now total count is 4 matching next surname as “Marcel” is count + 1 as 5 and goes on …. 

select dense_rank() over (order by Surname) as [DenseRank], * from finals 

DenseRank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
2 3 Dany Lowe Mathematics 75
2 4 Dany Lowe Lecture 85
3 5 Alice Marcel Mathematics 77
3 6 Alice Marcel Lecture 100
4 1 Bugra Postaci Mathematics 89
4 2 Bugra Postaci Lecture 90

 Dense rank its obvious like shown. 

 

if you want to make your denserank number reseting by groups there is a way use PARTITION BY
below example reseting ranks by using “partition by” by course column
select dense_rank() over (Partition by course order by Surname) as [DenseRank], * from finals  

DenseRank RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 4 Dany Lowe Lecture 85
3 6 Alice Marcel Lecture 100
4 2 Bugra Postaci Lecture 90
1 7 Simon Duru Mathematics 45
2 3 Dany Lowe Mathematics 75
3 5 Alice Marcel Mathematics 77
4 1 Bugra Postaci Mathematics 89

Thats all folks… 

Filed under T-SQL Tagged with , , , ,

NameObjectCollectionBase Sample c#

25/01/2010 Leave a comment

this is a  special collection in  System.Collections.Specialized . Main speaciality that it allows duplication keys to be added to the hashtable. It provides the abstract base class for a collection of associated String keys and Object values that can be accessed either with the key or with the index.

An Example:

 public sealed class ExecutionModuleCollection : NameObjectCollectionBase
    {
        private readonly object syncRoot = new object();
 
        public object SyncRoot
        {
            get { return this.syncRoot; }
        }
 
        public string[] AllKeys {
            get
            {
                return this.BaseGetAllKeys();
            }
        }
 
 
        public IExecutionModule this[int index]
        {
            get
            {
                return (IExecutionModule) this.BaseGet(index);
            }
            internal set
            {
                this.BaseSet(index, value);
            }
        }
 
        public IExecutionModule this[string name] {
            get
            {
                return (IExecutionModule) base.BaseGet(name);      
            }
            internal set
            {
                base.BaseSet(name, value);
            }
        }
 
 
        public void CopyTo(Array dest, int index)
        {
            Array.Copy(base.BaseGetAllValues(), dest, index);
        }
 
        public IExecutionModule Get(string name)
        {
            return (IExecutionModule)base.BaseGet(name);
        }
 
        public string GetKey(int index)
        {
            return  this.Keys[index].ToString() ;
        }
 
        public void Add(ExecutionModule module)
        {
            base.BaseAdd(module.Name, module);
        }
    }

Attention:

You can not insert value in specific order because it uses a hashtable in background and this kind of insertation break it.If you need it to be ordered, you should  use  an OrderedDictionary or SortedDictionary<T,T>.

About Thread Safety

Public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Enumerating through a collection is intrinsically not a thread safe procedure. Even when a collection is synchronized, other threads can still modify the collection, which causes the enumerator to throw an exception. To guarantee thread safety during enumeration, you can either lock the collection during the entire enumeration or catch the exceptions resulting from changes made by other threads.

Filed under C# Tagged with , , ,

What is the difference of Server.HtmlEncode and HttpUtility.HtmlEncode

22/01/2010 Leave a comment

There is no diffrence just Server.HtmlEncode calls  HttpUtility.HtmlEncode.
if you use reflector you realise that server object is kind of Httputility.

For more info:
http://msdn.microsoft.com/en-us/library/ms525347.aspx
http://msdn.microsoft.com/en-us/library/system.web.httputility.htmlencode.aspx

Filed under ASP.NET Tagged with ,

Hide your asp.net tag attribute from HTML output

22/01/2010 Leave a comment

You have an attribute and you want to hide this attribute in your HTML output . It useful when you dont want extra junk in your response then  use “meta:”

Example:

<asp:Label id=”myLabel” runat=”server”  text=”hello”  myattribute=”mello”  />

Output is

<span myattribute=”mello”>hello </span>

with using meta:

<asp:Label id=”myLabel” runat=”server”  text=”hello”  meta:myattribute=”mello”  />

output is

<span>hello</span>

May the code be with you…

Filed under ASP.NET Tagged with , ,

SyncRoot , lock(this) and Syncronized Pattern

21/01/2010 Leave a comment

You have an object and you want to use one of function that object as threadsafe you can use lock(object) statement for syncronization. But it can cause a deadlock if you are using “lock(this) ” . it occurs when derived classes want to try lock before your inner function execute and also it never gonna execute , because of your inner function wating first lock to be removed but it never be done here is the DeadLock!!  .

    public class MyClass
    {
        public void DoSomething()
        {
//DO NOT USE locking like this
            lock (this)
            {
               
            }
        }
    }

Dead Lock Example:

        MyClass cs = new MyClass();
        public void ThreadWorker() //this function is called by another thread
        {
            lock (cs) // DEADLOCK !!!
            {
                cs.DoSomething();
            }
        }

Here is the golden rule :

 If you have an internal data structure that you want to prevent simultaneous access to by multiple threads, you should always make sure the object you’re locking on is not public.

The reasoning behind this is that a public object can be locked by anyone, and thus you can create deadlocks because you’re not in total control of the locking pattern.
This means that locking on this is not an option, since anyone can lock on that object. Likewise, you should not lock on something you expose to the outside world.
Which means that the best solution is to use an internal object, and thus the tip is to just use Object.
Locking data structures is something you really need to have full control over, otherwise you risk setting up a scenario for deadlocking, which can be very problematic to handle.

Example

 public class MyClass
    {
// Use an inner object for locking.
        object _SyncRoot = new object();
        public void DoSomething()
        {
            lock (_SyncRoot)
            {
                int val = DateTime.Now.Second;
                Console.WriteLine("1) Do Someting Called by " + Thread.CurrentThread.Name);
                while (val - 1 != DateTime.Now.Second)
                {
                    //do some thing 
                }
                Console.WriteLine("1) out");
            }
        }
    }

Most of syncronization tasks  be used when working on collections  and Syncronized Pattern and  ICollection.SyncRoot Property is already exists for c# collections you dont need to create your own.
But if you have a custom object you can use  Syncronized Pattern for your classes . There is an example of Thread Safe wrapper code:

    class MyClass
    {
        private class SynchronizedClass : MyClass
        {
            private object syncRoot = new object();
            private MyClass _instance;
            public SynchronizedClass(MyClass cls) { _instance = cls; }
            public override bool IsSynchronized { get { return true; } }
            public override void Funk1() { lock (syncRoot) { _instance.Funk1(); } }
            public override void Funk2() { lock (syncRoot) { _instance.Funk2(); } }
        }
        public virtual bool IsSynchronized { get { return false; } }
        public static MyClass Synchronized(MyClass cls)
        {
            if (!cls.IsSynchronized)
                return new SynchronizedClass(cls);
            return cls;
        }
        public virtual void Funk1() { /* Your code here */ }
        public virtual void Funk2() { /* Your code here */ }
    }
See:
http://msdn.microsoft.com/en-us/library/system.collections.icollection.syncroot.aspx
http://stackoverflow.com/questions/728896/whats-the-use-of-the-syncroot-pattern
Happy Codding !!!

Filed under C# Tagged with , , , , , , , ,

← Older posts

Newer posts →