Difference between revisions of "Get the 11th to the 20th rows of the cia table - by population."

From SQLZOO
Jump to: navigation, search
(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...")
 
 
(2 intermediate revisions by one user not shown)
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'>
 
<div class='ht'>
</source>
+
<source lang='sql' class='def e-sqlite'>SELECT name, population FROM bbc
<source lang='sql' class='def e-sqlite'>SELECT name, population FROM cia
+
 
  ORDER BY population DESC LIMIT 10 OFFSET 10
 
  ORDER BY population DESC LIMIT 10 OFFSET 10
 
</source>
 
</source>
 
<source lang='sql' class='def e-access'>SELECT TOP 10 x.name, x.population FROM (
 
<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 population DESC) x
 
  ORDER BY x.population ASC
 
  ORDER BY x.population ASC
 
</source>
 
</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
 
  ORDER BY population DESC LIMIT 10 OFFSET 10
 
</source>
 
</source>
 
<source lang='sql' class='def e-oracle'>SELECT name, population FROM (
 
<source lang='sql' class='def e-oracle'>SELECT name, population FROM (
 
  SELECT name, population, rownum n FROM (
 
  SELECT name, population, rownum n FROM (
   SELECT name,population FROM cia
+
   SELECT name,population FROM bbc
 
                         ORDER BY population DESC)
 
                         ORDER BY population DESC)
 
  )
 
  )
Line 21: Line 20:
 
</source>
 
</source>
 
<source lang='sql' class='def e-sqlserver'>SELECT TOP 10 x.name, x.population FROM (
 
<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 population DESC) x
 
  ORDER BY x.population ASC
 
  ORDER BY x.population ASC
 
</source>
 
</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
 
  ORDER BY population DESC
 
  LIMIT 11, 10
 
  LIMIT 11, 10
 
</source>
 
</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-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:
 
Chris Sinclair of QuarterStaff proposes:
  
 
  SELECT name, population FROM (
 
  SELECT name, population FROM (
 
  SELECT name, population, rownum n FROM (
 
  SELECT name, population, rownum n FROM (
   SELECT name,population FROM cia
+
   SELECT name,population FROM bbc
 
                         ORDER BY population DESC)
 
                         ORDER BY population DESC)
 
   where rownum <=20
 
   where rownum <=20

Latest revision as of 11:26, 19 February 2013

Get the 11th to the 20th rows of the bbc table - by population.

SELECT name, population FROM bbc
 ORDER BY population DESC LIMIT 10 OFFSET 10
SELECT TOP 10 x.name, x.population FROM (
 SELECT TOP 20 name, population FROM bbc
  ORDER BY population DESC) x
 ORDER BY x.population ASC
SELECT name, population FROM bbc
 ORDER BY population DESC LIMIT 10 OFFSET 10
SELECT name, population FROM (
 SELECT name, population, rownum n FROM (
   SELECT name,population FROM bbc
                        ORDER BY population DESC)
 )
WHERE n BETWEEN 11 AND 20
SELECT TOP 10 x.name, x.population FROM (
 SELECT TOP 20 name, population FROM bbc
  ORDER BY population DESC) x
 ORDER BY x.population ASC
SELECT * FROM bbc
 ORDER BY population DESC
 LIMIT 11, 10
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense