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

SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

Hack 25.5 Splitting and combining columns

Hack 26 Include the rows your JOIN forgot

Hack 30 Calculate the maximum/minimum of two fields

Hack 33 Get values and subtotals in one shot

Hack 50 Combine tables containing different data

Hack 51/52 Display rows as columns

Hack 55 Import Someone Else's Data

Hack 62 Issue Queries Without Using a Table

Hack 63 Generate rows without tables

Hack 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page