RE: How to get second highest record usig sql/400.

Hi,

If you are on release V5R4 you can use OLAP Ranking Functions to archieve
this:
With x as (Select a.*,
rownumber() over(Order By Key1 Desc, Key2 Desc ...KeyN
Desc) as Counter
from MyTable a
Where ....)
Select a.*
from x
Where Counter = 2;

If you are not yet on release you may try the following:
With x as (Select a.*
from MyTable a
Where ...
Order By Key1 Desc, Key2 Desc ... KeyN Desc
Fetch First 2 Rows Only)
Select a.*
from x
Order By Key1, Key2, ... KeyN
Fetch First Row only;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Shailendra Tewatia
Gesendet: Thursday, April 19, 2007 06:41
An: rpg400-l@xxxxxxxxxxxx
Betreff: How to get second highest record usig sql/400.


Hi Gurus,
Is there any built in function to get the second highest record.


Advance thanks
Shail


DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Limited (MindTree) will not be responsible for
any viruses or defects or any forwarded attachments emanating either from
within MindTree or outside. If you have received this message by mistake
please notify the sender by return e-mail and delete this message from your
system. Any unauthorized use or dissemination of this message in whole or in
part is strictly prohibited. Please note that e-mails are susceptible to
change and MindTree shall not be liable for any improper, untimely or
incomplete transmission.