Skip to content

Instantly share code, notes, and snippets.

@cjmamo

cjmamo/dense-rank-offset-0-count-2-wrapped.sql

Last active June 27, 2017 06:05
Show Gist options
  • Save cjmamo/0ba4ad21df38dacee9d64258c0166da4 to your computer and use it in GitHub Desktop.
Save cjmamo/0ba4ad21df38dacee9d64258c0166da4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
dense-rank-offset-0-count-2-wrapped.sql
SELECT * FROM
(SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM
(SELECT *, DENSE_RANK() OVER (ORDER BY name, id) offset_ FROM
(SELECT a.id, a.name, b.title
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId) result) result_offset
WHERE offset_ > 0) result_offset_count
WHERE count <= 2
Raw
dense-rank-offset-0-count-2.sql
SELECT * FROM
(SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM
(SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId) result_offset
WHERE offset_ > 0) result_offset_count
WHERE count <= 2
Raw
dense-rank-offset-0-offset-2.sql
SELECT * FROM
(SELECT *, DENSE_RANK() OVER (ORDER BY name, id) offset_ FROM
(SELECT a.id, a.name, b.title
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId) result) result_offset
WHERE offset_ > 0 AND offset_ <= (0 + 2)
Raw
dense-rank-offset-1-count.sql
SELECT *, DENSE_RANK() OVER (ORDER BY name, id) count FROM
(SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId) result_offset
WHERE offset_ > 1
Raw
dense-rank-offset-1.sql
SELECT * FROM
(SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId) result_offset
WHERE offset_ > 1
Raw
dense-rank-offset.sql
SELECT a.id, a.name, b.title, DENSE_RANK() OVER (ORDER BY a.name, a.id) offset_
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId
Raw
limit-offset.sql
SELECT a.id, a.name, b.title
FROM AUTHOR a, BOOK b
WHERE a.id = b.authorId
ORDER BY a.name LIMIT 2 OFFSET 0
Raw
result-1.txt
ID | Name | Title
21 | Hintjens Peter | Confessions of a Necromancer
21 | Hintjens Peter | Social Architecture
21 | Hintjens Peter | ZeroMQ, Messaging for Many Applications
89 | Chomsky Noam | How the World Works
89 | Chomsky Noam | The Architecture of Language
89 | Chomsky Noam | What Kind of Creatures are We?
144 | Antonopoulos Andreas | The Internet of Money
144 | Antonopoulos Andreas | Mastering Bitcoin
144 | Antonopoulos Andreas | Mastering Ethereum
Raw
result-2.txt
ID | Name | Title
144 | Antonopoulos Andreas | The Internet of Money
144 | Antonopoulos Andreas | Mastering Bitcoin
Raw
result-3.txt
ID | Name | Title | offset_
144 | Antonopoulos Andreas | The Internet of Money | 1
144 | Antonopoulos Andreas | Mastering Bitcoin | 1
144 | Antonopoulos Andreas | Mastering Ethereum | 1
89 | Chomsky Noam | How the World Works | 2
89 | Chomsky Noam | The Architecture of Language | 2
89 | Chomsky Noam | What Kind of Creatures are We? | 2
22 | Hintjens Peter | Confessions of a Necromancer | 3
22 | Hintjens Peter | Social Architecture | 3
22 | Hintjens Peter | ZeroMQ, Messaging for Many Applications | 3
Raw
result-4.txt
ID | Name | Title | offset_
89 | Chomsky Noam | How the World Works | 2
89 | Chomsky Noam | The Architecture of Language | 2
89 | Chomsky Noam | What Kind of Creatures are We? | 2
22 | Hintjens Peter | Confessions of a Necromancer | 3
22 | Hintjens Peter | Social Architecture | 3
22 | Hintjens Peter | ZeroMQ, Messaging for Many Applications | 3
Raw
result-5.txt
ID | Name | Title | offset_ | count
89 | Chomsky Noam | How the World Works | 2 | 1
89 | Chomsky Noam | The Architecture of Language | 2 | 1
89 | Chomsky Noam | What Kind of Creatures are We? | 2 | 1
22 | Hintjens Peter | Confessions of a Necromancer | 3 | 2
22 | Hintjens Peter | Social Architecture | 3 | 2
22 | Hintjens Peter | ZeroMQ, Messaging for Many Applications | 3 | 2
Raw
result-6.txt
ID | Name | Title | offset_ | count
144 | Antonopoulos Andreas | The Internet of Money | 1 | 1
144 | Antonopoulos Andreas | Mastering Bitcoin | 1 | 1
144 | Antonopoulos Andreas | Mastering Ethereum | 1 | 1
89 | Chomsky Noam | How the World Works | 2 | 2
89 | Chomsky Noam | The Architecture of Language | 2 | 2
89 | Chomsky Noam | What Kind of Creatures are We? | 2 | 2
Raw
schema.sql
CREATE TABLE AUTHOR
(
id INTEGER PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE BOOK
(
id INTEGER PRIMARY KEY,
authorId INTEGER REFERENCES Author (id),
title VARCHAR(255)
);
Raw
sub-select.sql
SELECT a.id, a.name, b.title
FROM (SELECT * FROM AUTHOR ORDER BY name LIMIT 2 OFFSET 0) a, BOOK b
WHERE a.id = b.authorId
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment