Subset

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

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense