Query to fetch first and last row

andrevanzyl · #1 (**permalink**) 10-06-09, 05:08

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

Stealth_DBA · #2 (**permalink**) 10-06-09, 06:52

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.

Lenny77 · #3 (**permalink**) 10-06-09, 16:33

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

Code:

select t1.* from tabl1 t1
join
(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

tonkuma · #4 (**permalink**) 10-06-09, 18:55

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):
Need Help for a query

I like to use ROWNUMBER() OLAP expression, like this:

Code:

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech