Subset - Revision history

Connor at 10:02, 8 August 2012

2012-08-08T10:02:51Z

Connor at 10:02, 8 August 2012

2012-08-08T10:02:27Z

Connor: Created page with "
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 ..."

2012-08-08T10:01:39Z

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 ..."

New page

<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 different methods have to be used for the different platforms.</p>
<div class='ht'>
<div class=params>schema:scott</div>
<source lang=sql class='tidy'>DROP TABLE highscore</source>
<source lang=sql class='setup'>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);
</source>
<div>
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";
}
?>
</div>
<source lang='sql' class='def'>
SELECT username, score
FROM highscore
ORDER BY score DESC
LIMIT 10</source>
<source lang='sql' class='def e-oracle'>ALTER SESSION SET optimizer_goal=first_rows_10</source>
<source lang='sql' class='def e-sqlserver'>
SELECT username, score
FROM highscore
ORDER BY score DESC
OPTION (FAST 10)</source>
<source lang='sql' class='def e-access'>
SELECT TOP 10 username, score
FROM highscore
ORDER BY score DESC</source>

<div class="ecomm e-oracle" style="display: none">For oracle you can also use:
<pre>SELECT /*+first_rows(10) */ username, score
FROM highscore
ORDER BY score DESC</pre>
and:
<pre>SELECT username, score FROM
(SELECT username, score FROM highscore
ORDER BY score DESC) t
WHERE rownum <= 10</pre>
</div>
<div class="ecomm e-sqlserver" style="display: none">For sqlserver you can also use TOP:
<pre>SELECT TOP 10 username, score
FROM highscore
ORDER BY score DESC</pre></div>
</div>

{{Hacks Ref}}

@@ Line 36: / Line 36: @@
   $sql = "SELECT username, score FROM highscore "
         ."ORDER BY score DESC";
-  $cursor = mysql_query($sql) or die(mysql_error());
+  $cursor =
   $i = 0;
-  while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
+  while ($line =
    if ($i++>9) {break;}
    print "Position ".$i.",".$line{username}.",

@@ Line 30: / Line 30: @@
 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());
   $sql = "SELECT username, score FROM highscore "
         ."ORDER BY score DESC";
@@ Line 38: / Line 40: @@
   while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){
    if ($i++>9) {break;}
-   print "Position ".$i.",".$line{username}.",".$line{score}."\n";
+   print "Position ".$i.",".$line{username}.",
+  }
   ?>

Subset - Revision history

Connor at 10:02, 8 August 2012

Connor at 10:02, 8 August 2012

Connor: Created page with "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 ..."

Connor: Created page with "
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 ..."