Sub Query Record Limit

mohdalihpk · #1 (**permalink**) 03-10-10, 08:11

Dear All,
I am trying to execute a sub-query and it has multiple records but I want just one record for it. What is it's way.

I am thankful to you all who will trying to help me.

Yours,
Ali

ARWinner · #2 (**permalink**) 03-10-10, 08:35

You query is wrong. (Maybe if you post the query and what you want it to do you would get a better answer).

Andy

mohdalihpk · #3 (**permalink**) 03-10-10, 08:54

Select a.name from A a
where
exist (select dept from Dept where dept_loc like 'N%')

The sub-query has 200,000 records and I just want to limited it as if it return 200,000 record it just check if one is exist then it return true result rather it check all the 200000 records.

dav1mo · #4 (**permalink**) 03-10-10, 09:07

With the exists your subquery is not getting all 200K records, just the first one. Though with the query you show, you will get every row from table A, if a row does exist in your subquery.
Dave

ARWinner · #5 (**permalink**) 03-10-10, 09:08

You can add the FETCH FIRST 1 ROW ONLY clause to the subquery.

Andy

mohdalihpk · #6 (**permalink**) 03-10-10, 09:17

In sub-Query Fetch First 1Rows Only not allowed.

tonkuma · #7 (**permalink**) 03-10-10, 09:44

Quote:

In sub-Query Fetch First 1Rows Only not allowed.

What platform and DB2 version/release are you using?

By referencing manuals, fetch-first-clause in subquery was supported from
DB2 Universal Database(for LUW) Version 8,
DB2 Version 9.1 for z/OS,
and
DB2 for i Version 6 Release 1.

Vaibhav Vyas · #8 (**permalink**) 03-18-10, 07:53

You can use ROW_NUM function

tonkuma · #9 (**permalink**) 03-18-10, 09:14

Quote:

With the exists your subquery is not getting all 200K records, just the first one. Though with the query you show, you will get every row from table A, if a row does exist in your subquery.
Dave

Dave must be completely right.

My guess is that there might be some relationship between A and Dept,
something like...

Code:

SELECT a.name
  FROM A a
 WHERE 
       EXISTS
       (SELECT *
          FROM Dept
         WHERE Dept.dept = a.dept
           AND dept_loc LIKE 'N%')

Internet Services

Web Development

Digital Marketing

Consumer Tech