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.[My Video Promotion]
More from: SQL Code
- How to setup and run the Microsoft’s Command Line BCP Utility Without Installing SQL Server Client?
- Utility Class For Getting SQL Connection and Closing It
- Dropping Stored Procedure and Granting Execute Permission
- Finding MAX length of the data in DB column
- SQL – How To Find The Physical Location Of MySQL Database (Data Location)
- Quick SQL Reference – Find Duplicate Data In A Table Using Having Clause
- 005: MySQL – Selecting a set of random rows from a table
- SQL – Finding Nth Highest Row In DB Table
- SQL – How To Retrieve Data With Maximum Length?