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

Microservices vs. APIs

It still surprises me just how many times I come across misconceptions around Micro Services and APIs. Often hearing phrases like micro services are fine grained web services or API is themselves are equivalent to micro services. These all sort of show fundamental misconceptions under the covers. So, I've written this just to really break that out and explain about what the key differences are in those two concepts. What is an API? An API, fundamentally Application Programming Interface, that is an interface. It's a way of making requests into a component. So it's the route that you go in to make those requests. In modern use that typically means a REST API, that's a call made using HTTP protocol using JSON data as the payload. What are Micro Services? So let's ensure we also have a clear crisp definition on what a micro service architecture really is. Micro-Services architecture is about breaking down large silo applications into smalle...
Post a Comment
Read more

What's Virtual DOM?

There’s no big difference between the regular DOM and the virtual DOM. It’s better to think of the virtual DOM as React’s local and simplified copy of the HTML DOM. It allows React to do its computations within this abstract world and skip the real DOM operations, often slow and browser-specific. Real DOM operations are really really expensive. The Virtual DOM is an abstraction of the HTML DOM. It is lightweight and detached from the browser-specific implementation details.  One thing you should remember that the DOM itself was already an abstraction. So, Virtual DOM is an abstraction of an abstraction. :)
Post a Comment
Read more