SQL – Finding Nth Highest Row In DB Table

Generally I come across situations where I have to find nth highest row in a SQL table. The following piece of SQL code is very handy to me to deal with such situations. I thought of posting it to my blog thinking that it might help you either. Please replace N with proper level of highestness required, e.g. 1 (1st highest), 2 (2nd highest) and MYTABLE with your table, and MYCOLUMN with the column that you are trying to find the nth highest value from.

SELECT * FROM MYTABLE T1
WHERE (N =
(SELECT COUNT(DISTINCT (T2.MYCOLUMN))
FROM MYTABLE T2
WHERE T2.MYCOLUMN > T1.MYCOLUMN))

I have tested this code and it runs perfect.

SQL – How To Retrieve Data With Maximum Length?

Suppose you have a schema called mySchema and a table called myTable. You would like to retrieve all data with maximum length (for instance, whose first name is the longest).

The following is a simple query to handle situations like this.

select  * from 
mySchema.myTable 
where length(myTable.firstName)=  
(select max(length(myTable.firstName))  
from mySchema.myTable

Simple Huh ! Well it sometimes saves you a minute. The functions are tested to work in DB2/AS-400