Get the 11th to the 20th rows of the cia table - by population. - Revision history

PaigeHouse1971 at 11:26, 19 February 2013

2013-02-19T11:26:40Z

Connor at 09:55, 18 July 2012

2012-07-18T09:55:24Z

Connor at 09:14, 12 July 2012

2012-07-12T09:14:22Z

Connor: Created page with "Get the 11th to the 20th rows of the cia table - by population.
SELECT name, population FROM cia ORDER BY p..."

2012-07-11T15:17:43Z

Created page with "Get the 11th to the 20th rows of the cia table - by population. <div class='ht'> </source> <source lang='sql' class='def e-sqlite'>SELECT name, population FROM cia ORDER BY p..."

New page

Get the 11th to the 20th rows of the cia table - by population.
<div class='ht'>
</source>
<source lang='sql' class='def e-sqlite'>SELECT name, population FROM cia
ORDER BY population DESC LIMIT 10 OFFSET 10
</source>
<source lang='sql' class='def e-access'>SELECT TOP 10 x.name, x.population FROM (
SELECT TOP 20 name, population FROM cia
ORDER BY population DESC) x
ORDER BY x.population ASC
</source>
<source lang='sql' class='def e-postgres'>SELECT name, population FROM cia
ORDER BY population DESC LIMIT 10 OFFSET 10
</source>
<source lang='sql' class='def e-oracle'>SELECT name, population FROM (
SELECT name, population, rownum n FROM (
SELECT name,population FROM cia
ORDER BY population DESC)
)
WHERE n BETWEEN 11 AND 20
</source>
<source lang='sql' class='def e-sqlserver'>SELECT TOP 10 x.name, x.population FROM (
SELECT TOP 20 name, population FROM cia
ORDER BY population DESC) x
ORDER BY x.population ASC
</source>
<source lang='sql' class='def e-mysql'>SELECT * FROM cia
ORDER BY population DESC
LIMIT 11, 10
</source>
<div class="ecomm e-sqlserver" style="display: none">We select the bottom 10 of the top 20 - we end up with the list in the wrong order - this can be fixed with another nested select.</div>
<div class="ecomm e-oracle" style="display: none">You cannot usefully use rownum in the WHERE clause - however you can refer to it in an outer query.
Chris Sinclair of QuarterStaff proposes:

SELECT name, population FROM (
SELECT name, population, rownum n FROM (
SELECT name,population FROM cia
ORDER BY population DESC)
where rownum <=20
)
WHERE n >= 11

While this makes an insignificant difference to the performance of the query on the cia dataset, if the innermost select does not require a full table scan then the Oracle optimiser will stop it as soon as twenty rows have been returned rather than generating rownum records for the entire query and then discarding the excess. </div>
</div>
{{Meta Data ref}}

@@ Line 1: / Line 1: @@
 Get the 11th to the 20th rows of the bbc table - by population.
 <div class='ht'>
 <source lang='sql' class='def e-sqlite'>SELECT name, population FROM bbc
   ORDER BY population DESC LIMIT 10 OFFSET 10

@@ Line 29: / Line 29: @@
 </source>
 <div class="ecomm e-sqlserver" style="display: none">We select the bottom 10 of the top 20 - we end up with the list in the wrong order - this can be fixed with another nested select.</div>
-<div class="ecomm e-oracle" style="display: none">You cannot usefully use rownum in the WHERE clause - however you can refer to it in an outer query.
+<div class="ecomm e-oracle" style="display: none">You cannot usefully use rownum in the WHERE clause - however you can refer to it in an outer query. People generally need guarantees that they receive good grades when order [http://primewritings.com custom term paper] or purchase custom academic papers. Alas, not every academic writing service is able to give those guarantees.
 Chris Sinclair of QuarterStaff proposes:

@@ Line 1: / Line 1: @@
-Get the 11th to the 20th rows of the cia table - by population.
+Get the 11th to the 20th rows of the bbc table - by population.
 <div class='ht'>
 </source>
-<source lang='sql' class='def e-sqlite'>SELECT name, population FROM cia
+<source lang='sql' class='def e-sqlite'>SELECT name, population FROM bbc
   ORDER BY population DESC LIMIT 10 OFFSET 10
 </source>
 <source lang='sql' class='def e-access'>SELECT TOP 10 x.name, x.population FROM (
-  SELECT TOP 20 name, population FROM cia
+  SELECT TOP 20 name, population FROM bbc
    ORDER BY population DESC) x
   ORDER BY x.population ASC
 </source>
-<source lang='sql' class='def e-postgres'>SELECT name, population FROM cia
+<source lang='sql' class='def e-postgres'>SELECT name, population FROM bbc
   ORDER BY population DESC LIMIT 10 OFFSET 10
 </source>
 <source lang='sql' class='def e-oracle'>SELECT name, population FROM (
   SELECT name, population, rownum n FROM (
-    SELECT name,population FROM cia
+    SELECT name,population FROM bbc
                          ORDER BY population DESC)
+  )
@@ Line 21: / Line 21: @@
 </source>
 <source lang='sql' class='def e-sqlserver'>SELECT TOP 10 x.name, x.population FROM (
-  SELECT TOP 20 name, population FROM cia
+  SELECT TOP 20 name, population FROM bbc
    ORDER BY population DESC) x
   ORDER BY x.population ASC
 </source>
-<source lang='sql' class='def e-mysql'>SELECT * FROM cia
+<source lang='sql' class='def e-mysql'>SELECT * FROM bbc
   ORDER BY population DESC
   LIMIT 11, 10
@@ Line 35: / Line 35: @@
   SELECT name, population FROM (
   SELECT name, population, rownum n FROM (
-    SELECT name,population FROM cia
+    SELECT name,population FROM bbc
                          ORDER BY population DESC)
    where rownum <=20

Get the 11th to the 20th rows of the cia table - by population. - Revision history

PaigeHouse1971 at 11:26, 19 February 2013

Connor at 09:55, 18 July 2012

Connor at 09:14, 12 July 2012

Connor: Created page with "Get the 11th to the 20th rows of the cia table - by population. SELECT name, population FROM cia ORDER BY p..."

Connor: Created page with "Get the 11th to the 20th rows of the cia table - by population.
SELECT name, population FROM cia ORDER BY p..."