Subset

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

For oracle you can also use:

SELECT /*+first_rows(10) */ username, score
  FROM highscore
  ORDER BY score DESC

and:

SELECT username, score FROM
   (SELECT username, score FROM highscore
    ORDER BY score DESC) t
    WHERE rownum <= 10

For sqlserver you can also use TOP:

SELECT TOP 10 username, score
  FROM highscore
  ORDER BY score DESC

Your server today is: digitalocean-sqlzoo