Find the nth row in a table

/*Tested on SQL server 2005*/
/*Set up the test table*/
CREATE TABLE PS_TEST_RECORD (PERSON_ID NVARCHAR(15) NOT NULL,
AGE SMALLINT NOT NULL);
INSERT INTO PS_TEST_RECORD VALUES ('1',12);
INSERT INTO PS_TEST_RECORD VALUES ('2',19);
INSERT INTO PS_TEST_RECORD VALUES ('3',35);
INSERT INTO PS_TEST_RECORD VALUES ('4',20);
INSERT INTO PS_TEST_RECORD VALUES ('5',88);
INSERT INTO PS_TEST_RECORD VALUES ('6',52);
INSERT INTO PS_TEST_RECORD VALUES ('7',52);

/*Query 1*/

select * from (select PERSON_ID,AGE, dense_rank() over (order by AGE desc) rank from PS_TEST_RECORD) a where a.rank=2

/*Query 2*/

SELECT * FROM PS_TEST_RECORD T1
WHERE (RANK-1)=(SELECT count(DISTINCT(T2.AGE)) FROM PS_TEST_SQL T2 WHERE
T2.AGE>T1.AGE)
WHERE RANK = 1,2,3,4,...

Common Table Expression in SQL server - Recursive Queries

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
)
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
INSERT INTO dbo.MyEmployees VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
INSERT INTO dbo.MyEmployees VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
INSERT INTO dbo.MyEmployees VALUES (275, N'Michael', N'Blythe', N'Sales Representative',3,274)
INSERT INTO dbo.MyEmployees VALUES (276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
INSERT INTO dbo.MyEmployees VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
INSERT INTO dbo.MyEmployees VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
INSERT INTO dbo.MyEmployees VALUES (16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
INSERT INTO dbo.MyEmployees VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
SELECT * FROM MyEmployees


WITH DirectReports (ManagerID, EmployeeID, Title, Level)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title,
0 AS Level
FROM dbo.MyEmployees AS e
WHERE e.ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports ;

Search for particular value in all peoplesoft tables

I had a requirement where I needed to find all the tables / columns which contain an email address value.
I assumed the field which stores the email address to have length of atleast 30 chars.

This is a stored procedure to achieve that.

--DROP PROCEDURE dbo.SS_SearchInAllPSTables
CREATE PROCEDURE dbo.SS_SearchInAllPSTables (@in_param1 as varchar(50))

AS
DECLARE @a varchar(50);
DECLARE @c varchar(50);
DECLARE @MyCursor CURSOR ;

declare @SqlTabName Nvarchar(500)
declare @ParamDefinition Nvarchar(500)
declare @CntrValue as int
declare @in_param2 as varchar(50)

SET @MyCursor = CURSOR
FOR
SELECT CASE WHEN SUBSTRING (A.RECNAME,1,2)<>'PS' THEN 'PS_'+A.RECNAME ELSE A.RECNAME END,B.FIELDNAME
FROM PSRECDEFN A
, PSRECFIELDDB B
, PSDBFIELD C
WHERE A.RECNAME=B.RECNAME
AND A.RECTYPE=0
AND B.FIELDNAME=C.FIELDNAME
AND ((C.LENGTH>30
AND C.FIELDTYPE=0)
OR (C.FIELDTYPE=1))
AND A.RECNAME NOT LIKE '%AET'
AND A.RECNAME NOT LIKE '%TMP'
AND EXISTS(
SELECT '1'
FROM SYSOBJECTS
WHERE NAME=CASE WHEN SUBSTRING (A.RECNAME,1,2)<>'PS' THEN 'PS_'+A.RECNAME ELSE A.RECNAME END);

SET @ParamDefinition = N'@b varchar(10) output'

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @a ,@c

WHILE @@FETCH_STATUS = 0
BEGIN

select @SqlTabName = 'SELECT @b=COUNT(1) FROM ' + @a + ' where ' + @c +' like '+ '''%' + @in_param1 + '%''';

exec sp_executesql @statement = @SqlTabName, @parameters = @ParamDefinition ,@b = @CntrValue OUTPUT
IF @CntrValue>0
BEGIN
INSERT INTO PS_TEST_SEARCH_REC VALUES('SELECT * FROM '+@a +' where ' + @c +' like '+ '''%' + @in_param1 + '%'''+'-- COUNT ='+ cast(@CntrValue AS char(5)) );
END;
FETCH NEXT FROM @MyCursor
INTO @a ,@c
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

Find Nth Highest row from the table in ORACLE

In this example N=6

select bo_id_cust,count(1) from ps_rc_Case
where creation_date>'19-OCT-08'
group by bo_id_cust
HAVING COUNT(1)
IN (
SELECT A.COL3 FROM (
select bo_id_cust,count(1) COL3 from ps_rc_Case
where creation_date>'19-OCT-08'
group by bo_id_cust
HAVING COUNT(1)>1
) A
WHERE 6 =
( SELECT COUNT(DISTINCT B.COL3)
FROM (
select bo_id_cust,count(1) COL3 from ps_rc_Case
where creation_date>'19-OCT-08'
group by bo_id_cust
HAVING COUNT(1)>1 ) B
WHERE B.COL3>=A.COL3 )
)