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

by

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

More Related Contents:

  1. Join vs. sub-query
  2. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  3. How to use GROUP BY to concatenate strings in MySQL?
  4. Natural Sort in MySQL
  5. MySQL get row position in ORDER BY
  6. Subqueries vs joins
  7. MySQL Results as comma separated list
  8. MySQL unknown column in ON clause
  9. MySQL not using indexes with WHERE IN clause?
  10. SQL: Find the max record per group [duplicate]
  11. Group by month and year in MySQL
  12. calculate a sum of type time using sql
  13. MySQL: Transactions vs Locking Tables
  14. Maintaining order in MySQL “IN” query
  15. Remove duplicates using only a MySQL query?
  16. Group by date only on a Datetime column
  17. Finding the next available id in MySQL
  18. How exactly does using OR in a MySQL statement differ with/without parentheses?
  19. MySQL: How to copy rows, but change a few fields?
  20. How to renumber primary index
  21. Query that ignore the spaces
  22. Hamming distance on binary strings in SQL
  23. mysql automatically store record creation timestamp
  24. update columns values with column of another table based on condition [duplicate]
  25. How important are lookup tables?
  26. How to find all IDs of children recursively?
  27. MySql bulk load command line tool
  28. ALTER TABLE without locking the table?
  29. MySQL: Typecasting NULL to 0
  30. MySQL subquery returns more than one row

Leave a Comment