Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009

    Unanswered: Query to fetch first and last row

    Hi all

    I am new to writing queries for DB2, what I need to do is bring back the first row from a table as well as the last within one query. What is the best way to do this?

    Any help will be greatly appreciated.
    Thank you
    Andre van Zyl

  2. #2
    Join Date
    May 2009
    Provided Answers: 1
    andrevanzyl, A relational database has no concept of First or Last row. You have to supply something so that can be determined. This 'something' could be a unique sequential number, a timestamp, some sortable column, etc..

    Once you have this 'something', you can use the MIN and MAX functions or use and ORDER BY and FETCH FIRST 1 ROW ONLY (using two queries in Ascending and Descending order). The Row_Number function could also be used (and there may be other options).

    Without more information, I can't be any more specific.

  3. #3
    Join Date
    Jul 2009

    Post Using a key

    If rowId is unique key for this table, you can use:

    select t1.* from tabl1 t1
    (select min(rowId) MId from tabl1 
     Union All
     select max(rowId) MId from tabl1  ) t2
    On t1.rowId = t2.MId 
    order by  t1.rowId
    Lenny Khiger, ADSPA&VP

  4. #4
    Join Date
    Feb 2008
    It will be better to use primary key or unique column(s) than ROWID.
    One reason is meaningful order of rows for first and last rows.
    Another reason is repeatability.

    Here is a related discussion by Andy(Yesterday, 21:31 by ARWinner):

    I like to use ROWNUMBER() OLAP expression, like this:
    SELECT col1, col2, ...
      FROM (SELECT col1, col2, ...
                 , ROWNUMBER() OVER(ORDER BY sort_key1 ASC , sort_key2 ASC , ...) AS rn_asc
                 , ROWNUMBER() OVER(ORDER BY sort_key1 DESC, sort_key2 DESC, ...) AS rn_desc
              FROM <table>
             WHERE .....
           ) q
     WHERE rn_asc  = 1
       OR  rn_desc = 1

Posting Permissions

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