Select MAX, 2nd highest, etc.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carroll - 06 Dec 2007 22:23 GMT

I would like to be able to select the maximum (highest ) value for a
field, the 2nd highest, 3rd highest, etc. Are there any other
functions that I might consider using with SQL? MAX will only pick up
the highest and MIN the lowest value, but is there a way to pick up
middle values, i.e., the 2nd, 3rd, etc, so I can list them in one row,
along with a common key?

Thanks,

Carroll Rinehart

Reply to this Message

Serge Rielau - 06 Dec 2007 22:58 GMT

> I would like to be able to select the maximum (highest ) value for a
> field, the 2nd highest, 3rd highest, etc. Are there any other
> functions that I might consider using with SQL? MAX will only pick up
> the highest and MIN the lowest value, but is there a way to pick up
> middle values, i.e., the 2nd, 3rd, etc, so I can list them in one row,
> along with a common key?

SELECT key FROM
(SELECT ROW_NUMBER() OVER(ORDER BY key) AS rn FROM T) AS X
WHERE rn = <put your number here>

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message