Select the last and next five from the current timestamp in sql

I have a table with (order_id, timestamp). The timestamps represents the orders delivery date, which may occur in the future. How can i get the last 5 orders from now and the next 5 orders from now using one select statement? It it possible to do that in sql without using a union query? Something like this, but without th开发者_运维问答e union:

select * from table where timestamp <= current_timestamp
order by timestamp desc limit 5
union
select * from table where timestamp >= current_timestamp
order by timestamp asc limit 5

I think we can do this with a window function:

WITH Numbered AS (
   SELECT
       *, --TODO, pick columns
       ROW_NUMBER() OVER (ORDER BY CASE WHEN timestamp < current_timestamp THEN timestamp ELSE '18000101' END desc) as HistoricRN,
       ROW_NUMBER() OVER (ORDER BY CASE WHEN timestamp >= current_timestamp THEN timestamp ELSE '99991231' END) as FutureRN
    FROM table
)
SELECT
   * --TODO, pick columns
from Numbered
where HistoricRN between 1 and 5 or FutureRN between 1 and 5

Note I've arbitrarily decided that if the timestamp exactly matches, it will be in just the future rows. Your original query puts it in both groups (but then the UNION would eliminate it), so if a timestamp exactly matches, your query would return 9 rows instead of 10.

I don't think a union is such a bad idea but your query must be fixed. You need to embed your queries in sub-queries to be able to use order by and limit.

(Not tested in PostgreSQL).

select * from
  (select *
   from table
   where timestamp <= current_timestamp
   order by timestamp desc limit 5) as T
union
select * from 
  (select *
   from table
   where timestamp >= current_timestamp
   order by timestamp asc limit 5) as T

SQL Server provides an option in terms of TOP. Here are some examples

SELECT TOP 5 order_id, timestamp FROM table WHERE timestamp < current_timestamp

A quick search in WWW told me that there is no direct equivalent for this in Oracle. You are left with using rownum. As you know rownum is assigned before sorting you may not get actual result. Here is the workaround

SELECT e.*
  FROM (SELECT * FROM table WHERE timestamp < current_timestamp ORDER BY empno) e
 WHERE rownum <= 5