Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Sub Query Record Limit

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You query is wrong. (Maybe if you post the query and what you want it to do you would get a better answer).

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    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.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can add the FETCH FIRST 1 ROW ONLY clause to the subquery.


    Andy

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    In sub-Query Fetch First 1Rows Only not allowed.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Jul 2009
    Posts
    17
    You can use ROW_NUM function
    Vyas| Miracle Happens

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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%')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •