Subset

From SQLZOO
Revision as of 10:02, 8 August 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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