between max() and min()

nelsonpaixao

Hi, how can i get info on the 2nd max(id) or 3rd min(money) record on a table. I only know how to get min(X) or max(X) record on a table Thanks ;P

TheFM234

Select Max(id)
From table
where id != (Select Max(id) From table)

The above will give you the second maximum id.

Blue_Boy

Another way: select top 1 ColumName from TableName where ColumName < (select max(ColumName ) from TableName) order by TableName desc

I Love T-SQL "Don't torture yourself,let the life to do it for you."

MatthysDT

To select the 5th lowest (or n-th lowest) replace the "5" in this query with the number you want.

Select max(X) from
(Select top 5 id as X from idTable order by id asc)t

To select the n-th highest replace the "5" in this query with the number you want.

Select min(X) from
(Select top 5 id as X from idTable order by id desc) t

Cheers!