Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: Select a single row without fetch

    Windows 2008 64-bit.

    DB2 9.7 Fix Pack 4.

    I need to select a single row from a table. So I did this.

    Code:
    select accountnumber, timestamp, destination, login_id 
    from central.eventlog 
    where accountnumber = '8467284' 
    order by timestamp 
    fetch first row only;
    This finds the first instance in my eventlog table for a particular account number based on the time (it's not necessarily the first based on row number).

    This works fine. But I need to combine it with another query using UNION ALL. I can't use fetch first row only when using UNION ALL.

    How can I rewrite the above script to return the correct result without using fetch first row only?
    Last edited by Pryach; 06-22-12 at 03:19.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release/fixpack and platform OS are you using?

    ... I need to combine it with another query using UNION ALL.
    Please publish another query.

    Why do you need to combine the queries?

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Pryach View Post
    I need to select a single row from a table. So I did this.

    Code:
    select accountnumber, timestamp, destination, login_id 
    from central.eventlog 
    where accountnumber = '8467284' 
    order by timestamp 
    fetch first row only;
    This finds the first instance in my eventlog table for a particular account number based on the time (it's not necessarily the first based on row number).

    This works fine. But I need to combine it with another query using UNION ALL. I can't use fetch first row only when using UNION ALL.

    How can I rewrite the above script to return the correct result without using fetch first row only?
    Since SQL2008(?) your actually allowed to do order by and fetch first in nested subselects, and atleast 10.1 supports it:

    select * from (select * from lateral(values 1,2,3) T(a) order by a fetch first 1 rows only) union all select * from (select * from lateral(values 1,2,3) T(a) order by a fetch first 1 rows only)

    But I agree with Tonkuma, why you need to do such thing?

    /Lennart
    --
    Lennart

  4. #4
    Join Date
    Jun 2010
    Posts
    15
    Windows 2008 64-bit.

    DB2 9.7 Fix Pack 4.

    The second query is this:

    Code:
    select accountnumber, timestamp, destination, login_id 
    from central.eventlog 
    where accountnumber = '8467284' and destination like '%)'
    order by timestamp
    Within the eventlog table, there are about 50 rows for each accountnumber. I want the very first one (what's in the destination field varies) based on time and any other row where the destination column ends with a close parenthesis.

    Both of these queries works fine by their self, but I want to run them as a single query.

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Pryach View Post
    Windows 2008 64-bit.

    DB2 9.7 Fix Pack 4.
    I have verified the construction I used against 9.5 fixpak8.

    [...]
    Within the eventlog table, there are about 50 rows for each accountnumber. I want the very first one (what's in the destination field varies) based on time and any other row where the destination column ends with a close parenthesis.
    [...]
    What if the first one for a given account ends with ')', i.e. the expressions are not mutually exclusive, do you want one or two rows returned?

    /Lennart
    --
    Lennart

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ORDER BY and FETCH FIRST clause inside a subquery was supported at least from DB2 8.2 on LUW.

    So, Lennart's way should be worked.
    (select * from lateral(values 1,2,3) T(a) order by a fetch first 1 rows only) union all (select * from lateral(values 1,2,3) T(a) order by a fetch first 1 rows only)
    Note: outside "select * from " are not necessary.


    What if the first one for a given account ends with ')', i.e. the expressions are not mutually exclusive, do you want one or two rows returned?
    If answer was "want one row returned",
    replace "UNION ALL" by "UNION" or try this query.
    Example 1:
    Code:
    SELECT accountnumber , timestamp , destination , login_id
     FROM (SELECT el.*
                , ROW_NUMBER()
                     OVER( ORDER BY timestamp ) AS row_num
            FROM  central.eventlog AS el
            WHERE accountnumber = '8467284'
          ) s
     WHERE row_num = 1
       OR  destination like '%)'
     ORDER BY
           timestamp
    ;
    Last edited by tonkuma; 06-22-12 at 06:04. Reason: Add "ORDER BY and " to first sentence.

Posting Permissions

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