Skip to main content

Select N th Maximum - SQL Server 2005

Image
By
To Select Nth Maximum Value using TOP key word, You can try the following Stored Procedure.
CREATE PROCEDURE SP_SelectTopN
(@N INT)
/****************************************/
/* Created By : Loganathan V */
/* Created On: 21-Sep-2010, Tuesday */
/* Purpose : To Get the N th Top Value from*/
/*                Recordset. */
/* How to : EXEC SP_SelectTopN */
/*             : EXEC SP_SelectTopN 4 */
/****************************************/
AS
BEGIN
        DECLARE @P INT
        SELECT @P=@N
        SELECT TOP 1 * FROM
        (SELECT TOP (@P) * FROM SALARYTABLE ORDER BY SALARY DESC)  AS TOPNRECORDS ORDER BY SALARY ASC
END

To Run the Stored Procedure follow this:
 
EXEC SP_SelectTopN 4
 
To Select Nth Maximum Value without the use of TOP key word, You can try the following Stored Procedure.
CREATE PROCEDURE USP_SELECT_TOP_NTH
(@N INT = NULL)
AS
/****************************************/
/* Created By : Loganathan V */
/* Created On: 21-Sep-2010, Tuesday */
/* Purpose : Find N th Maximum */
/* How to : EXEC USP_SELECT_TOP_NTH */
/* : EXEC USP_SELECT_TOP_NTH 4 */
/****************************************/
BEGIN
   IF @N IS NULL
     BEGIN
        SELECT * FROM SALARYTABLE S1 WHERE (1-1)=
        (
        SELECT COUNT(DISTINCT(S2.SALARY)) FROM SALARYTABLE S2
       WHERE S2.SALARY>S1.SALARY)
     END
   ELSE
     BEGIN
         SELECT * FROM SALARYTABLE S1 WHERE (@N-1)=
         (
         SELECT COUNT(DISTINCT(S2.SALARY)) FROM SALARYTABLE S2
         WHERE S2.SALARY>S1.SALARY)
      END
END

To Run the Stored Procedure follow this:
EXEC USP_SELECT_TOP_NTH 3
 
Sometimes we need to pull out the top most records in percentage. In that situations we can use the PERCENT key word to pull out the records.
SELECT TOP 50 PERCENT * FROM SALARYTABLE
 
The Percentage Value must be between 0 and 100. The number of records returned by PERCENT will be the actual number of records by percentage.
Is this helpful to you? If yes then leave a comment on this.
Happy Querying....................
Labels:

Comments

Post a Comment

Popular posts from this blog

Face to Face Interview for 5+ years experienced .Net Developers

By
Interviewer: Hi Loganathan, Good morning. How are you? Me: Great, How are you? Interviewer: I'm good. Can brief about yourself? Me: Well, I started my career with .Net framework 2.0 then worked on 3.5 and 4.0 frameworks as well. So, I can work on any frameworks. I've no chance to develop a desktop application. From the begining I'm working as a pure Web developer. Initially started with ASP.Net web applications development and moved to Silverlight applications. I've well experience in C#, SQL Server 2005/2008 and WCF. I used Microsoft Enterprise Library Application blocks and LINQ in my applications. I worked in both Waterfall model and Agile model environments. Currently working as a Senior Developer. My roles and responsibilities are developing controls in Silverlight, writing WCF methods and consuming them in our applications, writing new stored procedures or modifying, doing unit testing and code review. Interviewer: Great, Can you explain about your curren...
2 comments
Read more

Large Volume of Dataset Transfer from WCF to Silverlight

By
Most of the times the developers, architects have the problem to retrieve large volume of data from WCF Service to Silverlight client applications. It’s a big head ache for developers. But it’s not up to that much problematic one. We can solve this problem by changing some property’s values in Web.config of WCF Service host, Silverlight application’s ServiceReference.clientconfig and Silverlight XAP hosted ASP.Net Applications. Here I’m going to explain the Web.config changes we need to retrieve large volume of data from WCF Service and also uploading large size of files to the Server. Last week, I was trying to figure out why my WCF service call always threw the generic NotFound exception when trying to retrieve large datasets. Even though, I set buffer limits to 2147483647 (int.MaxValue) in the Silverlight ServiceReferences.ClientConfig file and WCF Service configuration Section under web.config the problem was persisting. I tried so many things from Data Access Layer and UI....
3 comments
Read more

Multiplication Table in SQL Server

By
Multiplication Table in SQL Server This query gives the multiplication table from 1 to 10. DECLARE @A INT,                   @B INT,                   @C INT,                   @D VARCHAR (100)                   SELECT @A=1                  PRINT ' MULTIPLICATION TABLE 1-10' /****************************************/ /* Created By : Loganathan V */ /* Created On: 20-Sep-2010, Monday */ /* Purpose : Multiplication Table */ /* How to : RUN THE QUERY */ /****************************************/ WHILE (@A<=10) BEGIN   ...
5 comments
Read more

Unity vs MEF in Prism

By
Prism offering two injection containers named Unity and MEF. Both the containers have their own capabilities. Before choosing the container decide your environment needs. Some of the capabilities provided by both ( Unity & MEF ) containers include the following: Both register types with the container. Both register instances with the container. Both imperatively create instances of registered types. Both inject instances of registered types into constructors. Both inject instances of registered types into properties. Both have declarative attributes for marking types and dependencies that need to be managed. Both resolve dependencies in an object graph. Unity provides several capabilities that MEF does not: Unity resolves concrete types without registration. Unity resolves open generics. Unity uses interception to capture calls to objects and add additional functionality to the target object. MEF provides several capabilities that Unity does not:...
Post a Comment
Read more

One or more ActiveX controls could not be displayed because..... in Outlook

By
Some times We've facing this problem in Microsoft Outlook. While trying to add the images to our mails or Opening some emails. 1) Your current security settings prohibit running ActiveX controls on this page, or 2) You have blocked a publisher of one of the controls As a result, the page may not display correctly. To resolve this problem follow these steps. In Microsoft Outlook : Go to Tools > Options > Mail Format > Message Format Check the options " Use MS Office Word to ....". Happy Mailing..........................
Post a Comment
Read more