Question & Answer
Question
How to select the last N rows of a table in ascending order, in one SQL statement.?
Cause
Standard SQL syntax provides an easy way to select the first N rows:
select FIRST 10 customer_num,call_dtime from cust_calls;
However, there is no syntax option to select the last 10 (N) rows in ascending order.
Answer
Select the first N rows by ordering the table in descending order first. This provides the desired last N records, although the listing order is most recent to least recent:
select FIRST 10 customer_num ,call_dtime from cust_calls order by call_dtime DESC;
Example output:
customer_num call_dtime
127 2008-07-31 14:30
121 2008-07-10 14:05
110 2008-07-07 10:24
119 2008-07-01 15:00
106 2008-06-12 08:20
116 2007-12-21 11:24
116 2007-11-28 13:34
Informix Server has a method called 'Collection-Derived Tables' which can be used to adjust the order of the result set for the above query, while still permitting the whole operation in one step:
select * from table( multiset( select FIRST 10 customer_num , call_dtime from cust_calls order by call_dtime desc)) order by call_dtime ASC;
Example output:
customer_num call_dtime
116 2007-11-28 13:34
116 2007-12-21 11:24
106 2008-06-12 08:20
119 2008-07-01 15:00
110 2008-07-07 10:24
121 2008-07-10 14:05
127 2008-07-31 14:30
This provides the desired last N records, while preserving the listing order in a normal ascending sequence.
Was this topic helpful?
Document Information
Modified date:
03 June 2021
UID
swg21569085