Skip to content

Instantly share code, notes, and snippets.

@skyjia

skyjia/paging_with_key.sql

Last active March 18, 2016 14:52
Show Gist options
  • Save skyjia/a015006396fb5aaf08c9 to your computer and use it in GitHub Desktop.
Save skyjia/a015006396fb5aaf08c9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. skyjia renamed this gist Mar 18, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion sql_paging_with_key.sql → paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,4 +17,4 @@ SELECT * FROM (
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
  2. skyjia revised this gist Mar 18, 2016. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions sql_paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,18 +2,18 @@ SET @start_key=1005069;
    SET @paging_size = 20;

    SELECT * FROM (
    SELECT
    (@row_num:=@row_num + 1) AS `@row_num`,
    SELECT
    (@row_num:=@row_num + 1) AS `@row_num`,
    (@row_num_in_page:=CASE
    WHEN f.id = @start_key THEN 0
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    ELSE -1
    END)
    AS `@row_num_in_page`,
    WHEN f.id = @start_key THEN 0
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    ELSE -1
    END)
    AS `@row_num_in_page`,
    f.*
    FROM
    fabula as f,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
  3. skyjia revised this gist Mar 18, 2016. No changes.
  4. skyjia revised this gist Mar 18, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion sql_paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,7 @@ SELECT * FROM (
    FROM
    fabula as f,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
  5. skyjia revised this gist Mar 18, 2016. No changes.
  6. skyjia revised this gist Mar 18, 2016. No changes.
  7. skyjia revised this gist Mar 18, 2016. No changes.
  8. skyjia revised this gist Mar 18, 2016. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions sql_paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,17 +4,17 @@ SET @paging_size = 20;
    SELECT * FROM (
    SELECT
    (@row_num:=@row_num + 1) AS `@row_num`,
    @row_num_in_page:=CASE
    (@row_num_in_page:=CASE
    WHEN f.id = @start_key THEN 0
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    ELSE -1
    END
    END)
    AS `@row_num_in_page`,
    f.*
    FROM
    fabula as f,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
  9. skyjia revised this gist Mar 18, 2016. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions sql_paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,15 +6,15 @@ SELECT * FROM (
    (@row_num:=@row_num + 1) AS `@row_num`,
    @row_num_in_page:=CASE
    WHEN f.id = @start_key THEN 0
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    ELSE -1
    END
    AS `@row_num_in_page`,
    f.*
    FROM
    fabula as f,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;
  10. skyjia created this gist Mar 18, 2016.
    20 changes: 20 additions & 0 deletions sql_paging_with_key.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    SET @start_key=1005069;
    SET @paging_size = 20;

    SELECT * FROM (
    SELECT
    (@row_num:=@row_num + 1) AS `@row_num`,
    @row_num_in_page:=CASE
    WHEN f.id = @start_key THEN 0
    WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
    ELSE -1
    END
    AS `@row_num_in_page`,
    f.*
    FROM
    fabula as f,
    (SELECT @row_num:=0) AS t,
    (SELECT @row_num_in_page:=-1) AS t2
    ORDER BY f.time_happened DESC
    ) AS fo
    WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;