Skip to content

Instantly share code, notes, and snippets.

@jankkhvej

jankkhvej/example_.sql

Created December 28, 2016 14:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jankkhvej/cd6f70bd1f6ab157df88789f6aee1486 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jankkhvej/cd6f70bd1f6ab157df88789f6aee1486 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jankkhvej created this gist Dec 28, 2016.
    36 changes: 36 additions & 0 deletions example_.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    DROP TEMPORARY TABLE IF EXISTS `__ranked_query`;

    CREATE TEMPORARY TABLE `__ranked_query` (
    `__object_id` BIGINT UNSIGNED,
    `__rank` BIGINT UNSIGNED,
    KEY `idx_r_o` (`__rank`, `__object_id`),
    KEY `idx_o_r` (`__object_id`, `__rank`)
    ) engine MEMORY
    AS
    SELECT `__object_id`, `__rank` FROM (
    ? --subselect here
    ) AS `__src_query`;

    SET @__prev_id := NULL;
    SET @__next_id := NULL;

    SET @__obj_rank := (
    SELECT `__rank`
    FROM `__ranked_query`
    WHERE `__object_id` = ? -- ID here
    ORDER BY `__rank` DESC LIMIT 1
    );

    SET @__prev_id := (
    SELECT `__object_id`
    FROM `__ranked_query` WHERE `__rank` = (@__obj_rank - 1)
    );

    SET @__next_id := (
    SELECT `__object_id`
    FROM `__ranked_query` WHERE `__rank` = (@__obj_rank + 1)
    );

    DROP TEMPORARY TABLE `__ranked_query`;

    SELECT @__prev_id AS `id_prev`, @__next_id AS `id_next`;