How to retrieve 2nd record from a table

Hi,

I have a scenario like this. There is an employee table with Name and Salary as the fields.
I want to retrieve the second highest salary from this table. I have written a SQL Query like this:

SELECT DISTINCT SALARY FROM EMPLOYEE WHERE ROWID = 2 ORDER BY SALARY DESC.

When i try to run this on SQL/400, its giving an error, which says 'COLUMN ROWID IS NOT SPECIFIED IN THE TABLE'.

Can anyone help. Thanks in advance. Please reply ASAP.

Prema
premavaniAsked:
Who is Participating?
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:
DSPDBR will give a list of related objects (such as views and indexes). Using that output in conjuction with DSPFD should give you all the information you desire about their structure.

Dave
0
 
daveslaterCommented:
Hi
in sql you could try

c/exec sql
c+ declare salary cursor for
c+ select distinct salary from employee
c+ order by salary desc
c/end-exec

c/exec sql open salary
c/end-exec

 ** Highest salary
c/fetch from salary into :salary
c/end-execc

 ** next highest salary
c/fetch from salary into :salary
c/end-exec

c/exec sql close salary
c/end-exec

:salary is your program variable

Dave
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Dave is absolutely right. DB2/400 has no concept of ROWID (although, on second thought, I believe that was talked about coming in a future release ... maybe it's in V5R3?)

For performance reasons, I'd add "fetch first 2 rows only" to Dave's select statement.

HTH,
Dave (the other one)
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
premavaniAuthor Commented:
Hello Dave,

Thanks for your quick reply.

Is there any other solution? I dont want to use embedded SQL. I want to run a query on SQL/400, that returns the second highest salary in one shot.

I have one more query on embedded SQL.

How to find out the key fields and Indexes of a file?
Is there any URL that gives information on embedded SQL, normalisation (Basically DB2/400)?

Awaiting for your quick response.

Prema
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
How about this:

select                                            
  max(SALARY)                                      
from                                              
  EMPLOYEE                                  
where                                              
  SALARY < (select max(SALARY) from EMPLOYEE)

I suppose if you wanted the entire record, you could do this:

select * from EMPLOYEE where SALARY = (      
   select                                            
     max(SALARY)                                      
   from                                              
     EMPLOYEE
   where                                              
     SALARY < (select max(SALARY) from EMPLOYEE)
)

Regards,
Dave
0
 
premavaniAuthor Commented:
Hello Dave,

Thank u. This is what i was  looking for.

I had posted one more query on embedded SQL.
I am posting that question again, please answer to that.

How to find out the key fields and Indexes of a file?
Like we have a CL command 'DSPFFD' that gives the information about field names, their types, length and so on.
Similarly, i want to know the index of a file. Is there any CL command for that?

Prema
0
 
tliottaCommented:
Prema:

Review views in the QSYS2 library. This is where the system catalog is made available for queries. In particular, review the SYSINDEXES and SYSKEYS views from QSYS2. Much of what you'll want to know comes out of there.

Tom
0
 
premavaniAuthor Commented:
Thanks Tom and Dave for your valuable feedback.

Dave,

DSPDBR, will display the related objects. I want to review the key fields of all these related objects in one shot.

EG: There is a PF and 3 LF's related to that PF. How to view the key fields of all these LF's in one shot?

Reply ASAP.

Prema
0
 
tliottaCommented:
Prema:

What version of OS/400?

Tom

(BTW, ROWID() has been an SQL function for maybe a couple releases, but it's _not_ equivalent to a "row number".)
0
All Courses

From novice to tech pro — start learning today.