Yeah me too LOL… let me break it down for you… If the data was in a race and the MAX row is considered the Gold Medal… the user wants the Silver Medalist.
So because sysobjects exists for every database my use-case will work with that table cause then I don’t have to worry about creating a test environment.
USE CASE: I want to know the create date (crdate) of the 2nd newest object in sysobjects
So if we were gonna solve this problem back in the day your query might look a little something like this:
EXAMPLE 1
SELECT MAX(A.crdate) AS [CreateDate]
FROM dbo.sysobjects A
WHERE A.crdate < ( SELECT MAX(crdate)
FROM dbo.sysobjects)
or if you wanted to use a variable… something like this:
EXAMPLE 2
DECLARE @crdate datetime
SELECT @crdate = MAX(crdate)
FROM dbo.sysobjects
SELECT MAX(A.crdate) AS [CreateDate]
FROM dbo.sysobjects A
WHERE A.crdate < @crdate
Seems pretty basic… give me the max crdate row from sysobjects where the crdate does not equal the max crdate from sysobjects.
(Aside: Now one problem that I already can see with this is that if the crdate is exactly the same we’d actual skip down to the max row where crdate did not equal the max crdate but we wont talk about that scenario cause well this is confusing enough of an article to begin with… 
)
Let’s now throw on our SQL 2005/2008 hats and find another way. We’re going to use the ROW_NUMBER() function to produce the same result:
EXAMPLE 3
SELECT CreateDate
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY crdate DESC) AS RowNumber, crdate AS [CreateDate]
FROM dbo.sysobjects) AS OrderedCreateDates
WHERE RowNumber = 2
OK so you can walk through the code fairly easily, we’re selecting the ROW_NUMBER from an ordered list based on crdate in a descending order and also selecting the crdate… from there we’re querying that data and asking for RowNumber 2 (which is our max row that is NOT the max row)
At first glance this looks very much like a skinning the cat 3 different ways but if you throw on your DBA hat and look at some of the stats behind each of the examples you can see the following:
Example 1
- Scan Count = 2
- Logical Reads = 48
Example 2
- Scans Count = 2
- Logical Reads = 48
Example 3 (SQL 2005/2008)
- Scan Count = 1
- Logical Reads = 24
In using the ROW_NUMBER() function we end up only having to hit the table once as opposed to 2 different times. This may seem like small potatoes but if you look at it from a “Death by a Thousand Cuts” mentality… every little bit helps.
Now think of how you would do the same thing if you wanted the 3rd highest? 10th? Much cleaner with the ROW_NUMBER() function.
Enjoy!!
