Skip to main content

Select N th Maximum - SQL Server 2005

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

Multiplication Table in SQL Server

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

Large Volume of Dataset Transfer from WCF to Silverlight

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. Finall...
5 comments
Read more

Node.js for Everything

Lots of web applications using Javascript for front end for a long time. Javascript enabling people to build really really interactive great products, we can use Javascript on the front end and on the back end. So, what do we do on the back end? Node.js is the answer. Here we'll see some of the npm packages that'll help us to enable Javascript in back end as well. Load balancing is the main one when you plan to host your application server environment. Node offers pm2 package to easy your job. It's modern CLI process manager for Node apps with a builtin load-balancer. npm install pm2@latest -g pm2 start app.js Next thing is running applications continuously. Yeah, we've a node package named forever. It's a simple CLI tool for ensuring that a given node script runs continuously. npm install forever -g forever start app.js Enabling debugging in Javascript client applications are easy. Debugging a node application is sometimes a black box sometimes not, but one of the ...
Post a Comment
Read more