How to select first, last and previous and next via sql

Question

I am trying to create pagination of records from a sql statement, I was able to get next and previous for all but the last record shows the previous record as next due to the sql query only returning 1 row instead of 2. I am setting next based on the first result and previous as the second result.

The data:

Apple
Banana
Grape
Orange

The sql:

select * 
from (select top 1 fruit 
      FROM table 
      where fruit > 'Banana' 
      order by fruit asc) as x 

UNION ALL 

select * 
from (select top 1 fruit 
      FROM table 
      where fruit < 'Banana' order by fruit desc) as y

The result:

fruit
--------
Grape
Apple

This works great!

For the first record - I get the correct result for the first record being the true next. For the last record - I get one result which is the second query that gets the previous record. However I have no way of knowing its the last record so it outputs as Next instead of Previous.

How would I determine that is the last record and the single row being returned is actually the previous?

OR

How can I have the query return a NULL row for the Next query? So I can test against a NULL result?

BONUS POINTS!

How can I have one query that will give me first record, last record and next and previous so I could enable circular pagination? If on first record show last record as previous. If on last record show next record as first record?

If you want to have "first", "last", "next" etc., you need some column to **order by**, e.g. a *sequence number* or something - and then you need to specify that `ORDER BY` clause in your select statement. No `ORDER BY` --> no ordering of any kind, and you cannot define a *next* or *previous* without ordering. — marc_s, Nov 29 '12 at 12:00
Next should *(as you have)* be `WHERE fruit > 'banana' ORDER BY fruit ASC`. But your Previous definition should be `WHERE fruit < 'banana' ORDER BY fruit DESC`. *(With `>` changed to `<`. Or, is that jus a typo in the question?)* Also, to get `NULL`, try using `MAX(fruit)` and `MIN(fruit)` instead of using `TOP 1` with `ORDER BY`. — MatBailie, Nov 29 '12 at 12:01
@marc_s I am basing the ordering on A-Z the list is an alphabetical that the user can page through each item. — uber_n00b, Nov 29 '12 at 12:06

score 3 · Accepted Answer · answered Nov 29 '12 at 12:09

SELECT
  *
FROM
(
  SELECT
    MIN(fruit)   as first,
    MAX(fruit)   as previous
  FROM
    yourTable
  WHERE
    fruit < 'banana'
)
CROSS JOIN
(
  SELECT
    MIN(fruit)   as next,
    MAX(fruit)   as last
  FROM
    yourTable
  WHERE
    fruit > 'banana'
)

Or, shorter, but I'm not sure that it would be more performant...

SELECT
  MIN(CASE WHEN fruit < 'banana' THEN fruit END)   as first,
  MAX(CASE WHEN fruit < 'banana' THEN fruit END)   as previous,
  MIN(CASE WHEN fruit > 'banana' THEN fruit END)   as next,
  MAX(CASE WHEN fruit > 'banana' THEN fruit END)   as last
FROM
  yourTable

Or, as a final wierd option...

SELECT
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit < 'banana' ORDER by fruit ASC)  AS first,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit < 'banana' ORDER by fruit DESC) AS previous,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit > 'banana' ORDER by fruit ASC)  AS next,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit > 'banana' ORDER by fruit DESC) AS last

(This may be usefull if you have thousands+ of records, as each is search quickly and separately.)

THANK YOU! I appreciate the guidance. – uber_n00b Nov 29 '12 at 12:25 — uber_n00b, Nov 29 '12 at 12:25

How to select first, last and previous and next via sql

1 Answers1