Ranking Functions : ROW

Ranking Functions : ROW_NUMBER

One of the features that was included in SQL Server 2005 were ranking functions and I bumped into one of these last week, The ROW_NUMBER().
This function allows you to provide a sequential integer value to the results of your query. This comes in handy when you want to rank your data according to some criteria.
Let me throw an example…I have a table that has a set of student marls, I can use a query shown below to actually rank the students according to there marks.

SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Rank,
StudentName, Score
FROM Students
ORDER BY StudentName.

In the OVER() clause you can specify multiple order-by clauses.
This function also as its use when it comes to pagination of data. Take a look at the query below.

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, StundentName) AS rownum,
StudentName, Score
FROM Students) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, StudentName.

This query fetches the rows between 4 to 6.
You can find more about ranking functions here.

Comments

The maximum nametable character count quota (16384) has been exceeded

Some of our services were growing and the other day it hit the quote, I could not update the service references, nor was I able to run the WCFTest client. An error is diplayed saying " The maximum nametable character count quota (16384) has been exceeded " The problem was with the mex endpoint, where the XML that was sent was too much for the client to handle, this can be fixed by do the following. Just paste the lines below within the configuration section of the devenve.exe.config and the svcutil.exe.config files found at the locations C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE , C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin Restart IIS and you are done. The detailed error that you get is the following : Error: Cannot obtain Metadata from net.tcp://localhost:8731/ Services/SecurityManager/mex If this is a Windows (R) Communication Foundation service to which you have access, please check that you have enabled metadata publishing at the specified address. F...

Hosting WCF services on IIS or Windows Services?

There came one of those questions from the client whether to use II7 hosting or windows service hosting for WCF services. I tried recollecting a few points and thought of writing it down. WCF applications can be hosted in 2 main ways - In a Windows service - On IIS 7 and above When WCF was first released, IIS 6 did not support hosting WCF applications that support Non-HTTP communication like Net.TCP or Net.MSMQ and developers had to rely on hosting these services on Windows Services. With the release of IIS 7, it was possible to deploy these Non-Http based applications also on IIS 7. Following are the benefits of using IIS 7 to host WCF applications Less development effort Hosting on Windows service, mandates the creating of a Windows service installer project on windows service and writing code to instantiate the service, whereas the service could just be hosted on IIS by creating an application on IIS, no further development is needed, just the service implementa...

Task based Asynchronous pattern, Async & Await and .NET 4.5

One of the key features in .Net 4.5 is to write asynchronous programs much easier. So if I was to write asynchronous programs in .Net 2.0/3.5, I would either follow the event based model or the callback based model. For an example, a synchronous method that does intensive work (say the DoWork()) can be made asynchronous by using the following patterns 1) Implementing the IAsyncResult pattern. in this implementation, 2 methods are exposed for the DoWork() synchronous method, the BeginDoWork() and the EndDoWork() method. The user will call the BeingDoWork() passing in the required parameters and a callback of the delegate type AsyncCallback(IAsyncResult). The BeginDoWork() will spawn a new thread a return control back to the user. Once work is completed in the spawned method, as a last step, it will call the inform the AsyncResult implementation, which in turns will call the EndDoWork() (which is the callback that was passed in to the BeginDoWork()). 2) Implementing the event pattern. ...

Nairooz Nilafdeen's Blog

Search This Blog