Page 1 of 1

Sub Query Problem

PostPosted: Thu Mar 07, 2013 11:35 pm
by gunesh
Hi

I am trying to fetch 6th max or 8th min record.
so created query but not working anyone help me that what is wrong with this query.

6 Max Record

SELECT MIN(SAL) FROM EMP WHERE SAL IN (SELECT SAL FROM EMP FETCH FIRST 6 ROWS ONLY ORDER BY SAL DESC)


PLEASE LET ME KNOW WHAT IS WRONG WITH THIS QUERY

Re: Sub Query Problem

PostPosted: Fri Mar 08, 2013 12:10 am
by Akatsukami
It doesn't work, of course; you told us that yourself.

Re: Sub Query Problem

PostPosted: Fri Mar 08, 2013 12:57 am
by Robert Sample
Does "not working", as you referred to it, mean:
-- the query executes but does not produce any results
-- the query executes but produces too few records
-- the query executes but produces too many records
-- the query executes but something else happened
-- the query does not execute but no message is produced
-- the query does not execute and a system abend occurs
-- the query does not execute and a non-zero return code is returned
-- something else you did not explain?

Re: Sub Query Problem

PostPosted: Fri Mar 08, 2013 11:38 am
by gunesh
I am fresher in mainframe and looking for job.
Its my logic only i don't have mainframe db2 access so i can not check query with db2.
So i need you peoples help for looking job and build logic in mainframe.
And gain experience with you people.


Thanks and Regards

Gunesh Asatkar

Re: Sub Query Problem

PostPosted: Fri Mar 08, 2013 12:32 pm
by enrico-sorichetti
to learn and exercise DB2 You should consider downloading and installing on Your PC

http://www-01.ibm.com/software/data/db2 ... nload.html

Re: Sub Query Problem

PostPosted: Fri Mar 08, 2013 2:53 pm
by GuyC
subselect syntax diagram:
>-select-clause--from-clause--+--------------+----------------->
                              '-where-clause-'

>--+-----------------+--+---------------+----------------------->
   '-group-by-clause-'  '-having-clause-'

>--+-----------------+--+--------------------+-----------------><
   '-order-by-clause-'  '-fetch-first-clause-'


first ORDER BY, then FETCH FIRST clause :
SELECT MIN(SAL) FROM EMP WHERE SAL IN (SELECT SAL FROM EMP ORDER BY SAL DESC FETCH FIRST 6 ROWS ONLY )