In fact, but it is just use subselects to make any restriction what you
wanna to.
###############################
# OK.. NOW A COMPLETE EXAMPLE:
################################
CREATE TABLE Products (
Product_Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(100) NULL,
CreationDate DATETIME NULL,
Value DOUBLE NULL,
PRIMARY KEY(Product_Id)
);
INSERT INTO Products
( Name, CreationDate, Value ) VALUES
( "Notebook" , "2007-11-21 13:49:00" , 4000 ),
( "Pen" , "2007-11-21 14:30:00" , 2 ),
( "Nike" , "2007-12-21 16:00:00" , 200 ),
( "Mouse" , "2007-12-22 17:30:00" , 50 ),
( "Chair" , "2007-12-22 18:10:00" , 50 ),
( "Book" , "2007-11-22 21:00:00" , 30 ),
( "Airplaine" , "2007-11-23 04:30:00" , 200000 ),
( "Key" , "2007-11-23 06:30:00" , 1 ),
( "CD Player" , "2007-11-24 16:30:00" , 30 ),
( "Ferrari" , "2007-11-24 18:30:00" , 120000 ),
( "Table" , "2007-11-24 22:30:00" , 100 ),
( "T-shirt" , "2007-11-25 22:30:00" , 10 ),
( "Red Glasses" , "2007-11-25 23:30:00" , 20 ),
( "Nice Hat" , "2007-11-25 23:55:00" , 50 ),
( "Drink" , "2007-11-26 02:30:00" , 15 ),
( "Chuch Norris Movie" , "2007-11-26 12:10:00" , 10 ),
( "DVD Player" , "2007-12-26 21:15:00" , 200 );
-- SOME SELECT WITH THE FILTERS AS YOU WISH
SELECT * FROM Products ORDER BY CreationDate DESC LIMIT 10;
-- USING THAT WITH THE RANDOM FUNCTION
SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC LIMIT 10 )
as TheTenRecentRecords ORDER BY RAND() LIMIT 1;
#################
# on mysql:
#################
mysql>
mysql>
mysql>
mysql> CREATE TABLE Products (
-> Product_Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> Name VARCHAR(100) NULL,
-> CreationDate DATETIME NULL,
-> Value DOUBLE NULL,
-> PRIMARY KEY(Product_Id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO Products
-> ( Name, CreationDate, Value ) VALUES
-> ( "Notebook" , "2007-11-21 13:49:00" , 4000 ),
-> ( "Pen" , "2007-11-21 14:30:00" , 2 ),
-> ( "Nike" , "2007-12-21 16:00:00" , 200 ),
-> ( "Mouse" , "2007-12-22 17:30:00" , 50 ),
-> ( "Chair" , "2007-12-22 18:10:00" , 50 ),
-> ( "Book" , "2007-11-22 21:00:00" , 30 ),
-> ( "Airplaine" , "2007-11-23 04:30:00" , 200000 ),
-> ( "Key" , "2007-11-23 06:30:00" , 1 ),
-> ( "CD Player" , "2007-11-24 16:30:00" , 30 ),
-> ( "Ferrari" , "2007-11-24 18:30:00" , 120000 ),
-> ( "Table" , "2007-11-24 22:30:00" , 100 ),
-> ( "T-shirt" , "2007-11-25 22:30:00" , 10 ),
-> ( "Red Glasses" , "2007-11-25 23:30:00" , 20 ),
-> ( "Nice Hat" , "2007-11-25 23:55:00" , 50 ),
-> ( "Drink" , "2007-11-26 02:30:00" , 15 ),
-> ( "Chuch Norris Movie" , "2007-11-26 12:10:00" , 10 ),
-> ( "DVD Player" , "2007-12-26 21:15:00" , 200 );
Query OK, 17 rows affected (0.00 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC
LIMIT 1
0 ) as TheTenRecentRecords ORDER BY RAND() LIMIT 1
+------------+-------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+-------+---------------------+-------+
| 4 | Mouse | 2007-12-22 17:30:00 | 50 |
+------------+-------+---------------------+-------+
1 row in set (0.02 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC
LIMIT 1
0 ) as TheTenRecentRecords ORDER BY RAND() LIMIT 1
-> ;
+------------+-------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+-------+---------------------+-------+
| 4 | Mouse | 2007-12-22 17:30:00 | 50 |
+------------+-------+---------------------+-------+
1 row in set (0.02 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC
LIMIT 1
0 ) as TheTenRecentRecords ORDER BY RAND() LIMIT 1
-> ;
+------------+-------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+-------+---------------------+-------+
| 5 | Chair | 2007-12-22 18:10:00 | 50 |
+------------+-------+---------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC
LIMIT 1
0 ) as TheTenRecentRecords ORDER BY RAND() LIMIT 1
-> ;
+------------+-------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+-------+---------------------+-------+
| 15 | Drink | 2007-11-26 02:30:00 | 15 |
+------------+-------+---------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY CreationDate DESC
LIMIT 1
0 ) as TheTenRecentRecords ORDER BY RAND() LIMIT 1
-> ;
+------------+-------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+-------+---------------------+-------+
| 5 | Chair | 2007-12-22 18:10:00 | 50 |
+------------+-------+---------------------+-------+
1 row in set (0.00 sec)
####################
# And you cand do more...
####################
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY Value DESC LIMIT 5 )
as T
heFiveExpensiveRecords ORDER BY RAND() LIMIT 1;
+------------+----------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+----------+---------------------+-------+
| 1 | Notebook | 2007-11-21 13:49:00 | 4000 |
+------------+----------+---------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY Value DESC LIMIT 5 )
as T
heFiveExpensiveRecords ORDER BY RAND() LIMIT 1;
+------------+------------+---------------------+-------+
| Product_Id | Name | CreationDate | Value |
+------------+------------+---------------------+-------+
| 17 | DVD Player | 2007-12-26 21:15:00 | 200 |
+------------+------------+---------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM ( SELECT * FROM Products ORDER BY Value DESC LIMIT 5 )
as T
heFiveExpensiveRecords ORDER BY RAND() LIMIT 1;
+------------+-----------+---------------------+--------+
| Product_Id | Name | CreationDate | Value |
+------------+-----------+---------------------+--------+
| 7 | Airplaine | 2007-11-23 04:30:00 | 200000 |
+------------+-----------+---------------------+--------+
1 row in set (0.00 sec)
--
O Blaine - What the developing
/|\ should'nt have been
| thiagomata.blog.com
/ \
[Non-text portions of this message have been removed]
