If there are records that have a field containing "X", return them, else return a random record.
How the heck do you do this?
Add a comment
|
3 Answers
This is best done with 2 queries. The first returns the records where field='x'. If that's empty, then do a query for a random record with field!='x'. Getting a random record can be very inefficient as you'll see from the number of "get random record" questions on SO. Because of this, you really only want to do it if you absolutely have to.
Comments
Just the bit to select a random record would be very difficult and highly unefficient on large tables in mysql, in this website you can find one script to do it, it should be trivial to add your condition for 'x' and get the functionality you need.
Comments
Well, here is my example based on mysql.users table:
First, non existing records:
mysql> SELECT * FROM (select user, 1 as q from user where user like '%z' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%z' ) THEN 1 ELSE 0 END);
+--------+---+
| user | q |
+--------+---+
| drupal | 0 |
+--------+---+
1 row in set (0.00 sec)
Then, existing:
mysql> SELECT * FROM (select user, 1 as q from user where user like '%t' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%t' ) THEN 1 ELSE 0 END);
+------------------+---+
| user | q |
+------------------+---+
| root | 1 |
| root | 1 |
| debian-sys-maint | 1 |
| root | 1 |
+------------------+---+
4 rows in set (0.00 sec)
Maybe it will be useful, or maybe someone will be able to rewrite it in better way.
