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 next row or the previous one?

Answer 1

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 |
+------+---------------+---------------------+

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

Using Mike's MAX/MIN trick we can make previous\next jumps for all sorts of things. This msAccess example will return the previous close for every record in a stock market data table. Note: the '<=' is for weekends and holidays.

SELECT 
   tableName.Date,
   tableName.Close,
   (SELECT Close 
      FROM tableName 
      WHERE Date = (SELECT MAX(Date) FROM tableName 
                     WHERE Date <= iJoined.yesterday)
   ) AS previousClose
FROM 
 (SELECT Date, DateAdd("d",-1, Date) AS yesterday FROM tableName)
  AS iJoined 
INNER JOIN 
    tableName ON tableName.Date=iJoined.Date;

...'yesterday' demonstrates using a function(Date-1) jump; we could have simply used...

(SELECT Date FROM tableName) AS iJoined
  /* previous record */
(SELECT MAX(Date) FROM tableName WHERE Date < iJoined.Date)
  /* next record */
(SELECT MIN(Date) FROM tableName WHERE Date > iJoined.Date)

The trick is we can previous\next # of whatever(s) with MAX\MIN and a jump function()

Answer 5

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 6

I considered id as primary key in the table (and as "row number"), and used it to compare each record with the record before. The following code must work.

CREATE SCHEMA temp
create table temp.emp (id integer,name varchar(50), salary  varchar(50));
insert into temp.emp values(1,'a','25000');
insert into temp.emp values(2,'b','30000');
insert into temp.emp values(3,'c','35000');
insert into temp.emp values(4,'d','40000');
insert into temp.emp values(5,'e','45000');
insert into temp.emp values(6,'f','20000');

select * from temp.emp

SELECT
    current.id, current.name, current.salary,
    case 
        when current.id = 1 then current.salary 
        else 
            case
                when current.salary > previous.salary then previous.salary
                else current.salary  
            end
     end
FROM
    temp.emp AS current
    LEFT OUTER JOIN temp.emp AS previous
    ON current.id = previous.id + 1