Pages

Thursday, February 18, 2010

SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

If you want to retrieve both top and bottom records both together using a T-SQL then you can use either of the following scripts.

Script 1 :

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail)
GO

Script 2 :

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
GO



Monday, February 8, 2010

SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 5th highest salary from Employee table ,

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 5 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary


If you want to retrieve a nth Maximum value then use nth value instead of 5 and enjoy!!!!!!!

Cheers

Tuesday, February 2, 2010

List All Stored Procedure Modified in Last N Days

I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 10 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT
name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 10
----Change 10 to any other day value

Following script will provide name of all the stored procedure which were created in last 10 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 10
----Change 10 to any other day value.

Date condition in above script can be adjusted to retrieve required data.