SA Developer .NET

Good day,

How does one select the second smallest value in a DateTime column if min(columnName) returns the smallest value?

Thanx.

simple,

select min(ColumnName) from TableName where ColumnName > min(ColumnName)

That works gr8 if your dates are unique. What happens if you have two smallest dates that are the same?

in SQL server you should be able to : 

select max(a.columnname) from (select top 2 ColumnName from TableName order by ColumnName asc) a

in Mysql 

select max(a.ColumnName) from (select ColumnName from TableName order by ColumnName asc Limit 2) a

In plain english... get the two smallest dates. Give me the largest of those two. That method will obviously work for the 3rd smallest, 4th smallest, etc..

The only downside to that is it would be quite slow to execute... anyone have a faster way?

Cheers

Paul 

If you're using SQL 2005 you can take advantage of ROW_NUMBER (note: RANK and DENSE_RANK will put duplicate ranking values for duplicate item values, so don't try use them) as in:

SELECT CustomerID, Name, DateAdded, ROW_NUMBER() OVER(ORDER BY DateAdded DESC) AS DateRank WHERE DateRank = 2

(Note 2: I haven't tested the above, as I'm too lazy. It should work, but your results may vary:-P)