sql pulling a row for next or previous row of a current row

Question

id    |  photo title     |  created_date

XEi43 |  my family       |  2009 08 04
dDls  |  friends group   |  2009 08 05
32kJ  |  beautiful place |  2009 08 06
EOIk  |  working late    |  2009 08 07 

Say I have the id 32kJ. How would I get the new or previous row?

Answer 1

Horrible hack - I don't like this but might work..

with yourresult as
(
select id, photo_title, created_date, ROW_NUMBER() over(order by created_date) as 'RowNum' from your_table
)
-- Previous
select * from yourresult where RowNum = ((select RowNum from yourresult where id = '32kJ') -1)
-- Next
select * from yourresult where RowNum = ((select RowNum from yourresult where id = '32kJ') +1)

That of any use?

Answer 2

I realize that you are using MySQL, but just for reference, here is how you would do this using Oracle's analytic functions LEAD and LAG:

select empno, ename, job,
  lag(ename, 1) over (order by ename) as the_guy_above_me,
  lead(ename, 2) over (order by ename) as the_guy_two_rows_below_me
from emp
order by ename

I guess there's a reason why Oracle costs money and MySQL is free... :-)

This page shows you how to emulate analytic functions in MySQL.

Answer 3

Did you want the next/previous row by date? If so, you could do this:

select MyTable.*
from MyTable
join
  (select id
   from MyTable
   where created_date < (select created_date from MyTable where id = '32kJ')
   order by created_date desc, id desc
   limit 1
  ) LimitedTable on LimitedTable.id = MyTable.fund_id;

Answer 4

This is what I use for finding previous/next records. Any column in your table can be used as the sort column, and no joins or nasty hacks are required:

Next record (date greater than current record):

SELECT id, title, MIN(created) AS created_date
FROM photo
WHERE created >
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

Previous record (date less than current record):

SELECT id, title, MAX(created) AS created_date
FROM photo
WHERE created <
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

Example:

CREATE TABLE `photo` (
    `id` VARCHAR(5) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `created` DATETIME NOT NULL,
    INDEX `created` (`created` ASC),
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('XEi43', 'my family',       '2009-08-04');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('dDls',  'friends group',   '2009-08-05');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('32kJ',  'beautiful place', '2009-08-06');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('EOIk',  'working late',    '2009-08-07');

SELECT * FROM photo ORDER BY created;
+-------+-----------------+---------------------+
| id    | title           | created             |
+-------+-----------------+---------------------+
| XEi43 | my family       | 2009-08-04 00:00:00 |
| dDls  | friends group   | 2009-08-05 00:00:00 |
| 32kJ  | beautiful place | 2009-08-06 00:00:00 |
| EOIk  | working late    | 2009-08-07 00:00:00 |
+-------+-----------------+---------------------+


SELECT id, title, MIN(created) AS next_date
FROM photo
WHERE created >
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

+------+--------------+---------------------+
| id   | title        | next_date           |
+------+--------------+---------------------+
| EOIk | working late | 2009-08-07 00:00:00 |
+------+--------------+---------------------+

SELECT id, title, MAX(created) AS prev_date
FROM photo
WHERE created <
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

+------+---------------+---------------------+
| id   | title         | prev_date           |
+------+---------------+---------------------+
| dDls | friends group | 2009-08-05 00:00:00 |
+------+---------------+---------------------+