Retrieving Nth Salary in SQL Server 2000 and 2005

Recently I came across a post in ASP.Net Forum where a user had asked for a Query for Nth Salary retrieval from employee table using SQL query. Strangely on further searching the net I found quite a few people had posted similar query on the net with no answered, here is my answer

SQL Server 2000

Declare @Table Table
(
EID INT Identity,
ESalary INT
)

Insert into @Table(ESalary)
Values (400)

Insert into @Table(ESalary)
Values (4100)

Insert into @Table(ESalary)
Values (1400)

Insert into @Table(ESalary)
Values (2400)

Insert into @Table(ESalary)
Values (4300)


Select * From @Table [T1] Where
(2 = (Select Count(Distinct [ESalary]) From @Table [T2] where [T1]।[ESalary] <= [T2].[ESalary])) Just replace 2 by any digit 3,4,5। and it will work

SQL Server 2005

SQL Server 2005 provides a new function, Row_Number(), for generating row numbers, this function is used in conjunction with over for generating Ranks/ Rowids staring from 1 for the first row in each partition. I will be posting in more detail regading this feature in my next post. meanwhile you should be able to see how simple the job has become with SQL Server 2005

SELECT

*

FROM (

SELECT EID,ESalary,row_number() OVER (ORDER BY ESalary) AS Rank

FROM @Table

) t1

WHERE Rank=2

हेमंत गुप्ता

Ten CSS tricks you may not know

I Liked this post on CSS on Digg.com. The author has done a good job of explaining CSS Tricks by Example.

Cascading Style Sheets are the foundation on which many of the best websites are built. Using CSS allows developers to describe the common style for the website, in terms of colours, fonts and layouts. In this tutorial, Trenton Moss of Webcredible shares some of his top tips to help you get the most from your CSS.

read more

हेमंत गुप्ता

Enterprise Library

Enterprise Library is what can be called .NET 2.0's version of application blocks used in .NET 1.1. I had a ball using application blocks in 1.1 and hope the same for Enterprise Library. below is a brief description as found on MSDN site and some other links i found. i have yet to use them extensively thus i will reserve my comments.

The Enterprise Library application blocks help address the common problems that developers face from one project to the next. They are designed to encapsulate the Microsoft recommended best practices for .NET applications. In addition, they can be added to .NET applications quickly and easily. For example, the Data Access Application Block provides access to the most frequently used features of ADO.NET 2.0 in simple-to-use classes, thus boosting developer productivity. It also addresses scenarios not directly supported by the underlying class libraries.
Different applications have different requirements, and you will not find that every application block is useful in every application that you build. Before using an application block, you should have a good understanding of your application requirements and of the scenarios that the application block is designed to address.

Enterprise Library 3.1–May 2007 contains the following general purpose application blocks:

Caching Application Block. Developers can use this application block to incorporate a local cache in their applications.
Cryptography Application Block. Developers can use this application block to incorporate hashing and symmetric encryption in their applications.
Data Access Application Block. Developers can use this application block to incorporate standard database functionality in their applications.
Exception Handling Application Block. Developers and policy makers can use this application block to create a consistent strategy for processing exceptions that occur throughout the architectural layers of enterprise applications.
Logging Application Block. Developers can use this application block to include standard logging functionality in their applications.
Policy Injection Application Block. Developers can use this application block to implement interception policies that can be used to streamline the implementation of common features, such as logging, caching, exception handling, and validation, across an application.
Security Application Block. Developers can use this application block to incorporate authorization and security caching functionality in their applications.
Validation Application Block. Developers can use this application block to create validation rules for business objects that can be used across different layers of their applications.
Enterprise Library also includes a set of core functions, including configuration, instrumentation, and object builder services। These functions are used by all other application blocks.

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

http://www.devx.com/dotnet/Article/30910

Have a good time. also those who read my blog by pure chance, please leave a comment on how you find my blog.



हेमंत गुप्ता

Membership and Roles in ASP.NET 2.0

Membership is a cool feature from MS which frees the developer from the hassels to creating a user (Registartion Process), Validating the user at login, or roles access across the site. just a great new feature, i am relatively new to the feature thus i am just sharring a few very good links that will help you.

ASP.NET membership gives you a built-in way to validate and store user credentials. ASP.NET membership therefore helps you manage user authentication in your Web sites. You can use ASP.NET membership with ASP.NET Forms authentication or with the ASP.NET login controls to create a complete system for authenticating users.

http://weblogs.asp.net/scottgu/archive/2006/02/24/ASP.NET-2.0-Membership_2C00_-Roles_2C00_-Forms-Authentication_2C00_-and-Security-Resources-.aspx (Scott Gu, ASP.NET Guru)

http://odetocode.com/Articles/427.aspx

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

http://msdn2.microsoft.com/en-us/library/yh26yfzy.aspx

http://www.awprofessional.com/articles/article.asp?p=351414&rl=1

Links provided above are in not particular order, i suggest you open all links once and decide your reading pattren your self

हेमंत गुप्ता

SQL Server 2000 Vs SQL Server 2005

When ever a new version of some product you have been using for long, you come across the familiar question why? why upgrade, do i really need it. well here are some articles to make up your mind

http://www.sqlservercentral.com/columnists/sjones/2988.asp

http://www.sqlservercentral.com/columnists/sjones/3036.asp

http://www.microsoft.com/sql/prodinfo/features/default.mspx

http://support.microsoft.com/kb/906773