Difference between revisions of "Subset"

From SQLZOO
Jump to: navigation, search
(Created page with "<p>In this example you are shown how to get subsets from tables.</p> <p>You may have a table with thousands of entries but you only want to pick the top 10,</p> <p>to do this ...")
 
Line 30: Line 30:
 
To get the top 10 you could write in PHP:
 
To get the top 10 you could write in PHP:
 
  <?
 
  <?
  mysql_connect('localhost','username','password') or die(mysql_error());
+
  mysql_connect('localhost','username','password')  
  mysql_select_db('dbname')                        or die(mysql_error());
+
  or die(mysql_error());
 +
  mysql_select_db('dbname')                         
 +
  or die(mysql_error());
 
  $sql = "SELECT username, score FROM highscore "
 
  $sql = "SELECT username, score FROM highscore "
 
       ."ORDER BY score DESC";
 
       ."ORDER BY score DESC";
Line 38: Line 40:
 
  while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
 
  while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
 
   if ($i++>9) {break;}
 
   if ($i++>9) {break;}
   print "Position ".$i.",".$line{username}.",".$line{score}."\n";
+
   print "Position ".$i.",".$line{username}.",
 +
                  ".$line{score}."\n";
 
  }
 
  }
 
  ?>
 
  ?>

Revision as of 10:02, 8 August 2012

In this example you are shown how to get subsets from tables.

You may have a table with thousands of entries but you only want to pick the top 10,

to do this different methods have to be used for the different platforms.

schema:scott
DROP TABLE highscore
CREATE TABLE highscore (
   username VARCHAR(20),
   score INT );
INSERT INTO highscore VALUES ('gordon',10);
INSERT INTO highscore VALUES ('user01',20);
INSERT INTO highscore VALUES ('user02',30);
INSERT INTO highscore VALUES ('user03',40);
INSERT INTO highscore VALUES ('user04',50);
INSERT INTO highscore VALUES ('user05',60);
INSERT INTO highscore VALUES ('user06',70);
INSERT INTO highscore VALUES ('user07',80);
INSERT INTO highscore VALUES ('user08',90);
INSERT INTO highscore VALUES ('user09',100);
INSERT INTO highscore VALUES ('user10',110);
INSERT INTO highscore VALUES ('user11',120);

For testing purposes we have the following:

CREATE TABLE highscore (username VARCHAR(20), score INT);
INSERT INTO highscore VALUES ('gordon',10);
INSERT INTO highscore VALUES ('user01',20);
...
INSERT INTO highscore VALUES ('user02',120);

To get the top 10 you could write in PHP:

<?
mysql_connect('localhost','username','password') 
  or die(mysql_error());
mysql_select_db('dbname')                        
  or die(mysql_error());
$sql = "SELECT username, score FROM highscore "
      ."ORDER BY score DESC";
$cursor = mysql_query($sql) or die(mysql_error());
$i = 0;
while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
 if ($i++>9) {break;}
 print "Position ".$i.",".$line{username}.",
                 ".$line{score}."\n";
}
?>
SELECT username, score
  FROM highscore
  ORDER BY score DESC
  LIMIT 10
ALTER SESSION SET optimizer_goal=first_rows_10
SELECT username, score
  FROM highscore
  ORDER BY score DESC
  OPTION (FAST 10)
SELECT TOP 10 username, score
  FROM highscore
  ORDER BY score DESC