Multiple SELECTs in SQLite

Databases
1

I’m using SQLite and currently have a simple query to select 6 random videos.

SELECT address, title FROM amvideos ORDER BY RANDOM() LIMIT 6;

The client now wants one video to be fixed, with the other 5 selected at random. It seems to me that if I could combine the following 2 queries in some way so they produce a single results set, it would do the job. Can it be done? Or is there another way to achieve the desired result?

SELECT address, title FROM amvideos WHERE address = '123456';
SELECT address, title FROM amvideos WHERE address != '123456' ORDER BY RANDOM() LIMIT 5;
2

Hmmm, does SQLite have a concat?

ermm, nvm, I think as long as both SELECTs return the same fields a UNION should work, no?

If the “fixed” needs to be first it might need an AS and an ORDER BY

1 Like
3

I’ve never used anything other than a simple query before

SELECT address, title FROM amvideos WHERE address = '123456'
UNION
SELECT address, title FROM amvideos WHERE address != '123456' ORDER BY RANDOM() LIMIT 5;

results in False.

4

I’m sure there must be a way. It’s 5AM here and I need to get some sleep. But if nobody beats me to it I’ll see if I can come up with a JOIN or something that will work later today.

1 Like
5

what?

6 
$query  = 'SELECT address, title FROM amvideos WHERE address = `275293411`
           UNION 
           SELECT address, title FROM amvideos WHERE address != `275293411` ORDER BY RANDOM() LIMIT 5;';
$result = $db->query($query);
var_dump($result);

gives bool(false).

I realise looking at it now that that is not going to do what I want anyway as it will still order everything by random.

7

Dwight Schrute would be proud

yeah, i was also gonna comment on the ORDER BY applying to the whole query and how you would want UNION ALL instead of UNION

this may be a tough pill to swallow, but you could just run two queries, right?

1 Like
8

I guess it might just be easier. :slight_smile:

Afraid you’ve lost me with Dwight Schrute.

9

google Dwight Schrute false images

3 Likes
10

I’ve gotten into the habit of working up MySQL queries in the command line and putting them into PHP after they are what I want. eg.

mysql> CREATE TABLE `amvideos` (
    ->   `id` int(2) NOT NULL AUTO_INCREMENT
    ->   , `address` varchar(20) DEFAULT NULL
    ->   , `title` varchar(10) DEFAULT NULL
    ->   , PRIMARY KEY (`id`)
    ->   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO `amvideos` (`address`, `title`)
    -> VALUES ('1234567890', 'vid1')
    ->   , ('2345678901', 'vid2')
    ->   , ('3456789012', 'vid3')
    ->   , ('4567890123', 'vid4')
    ->   , ('5678901234', 'vid5')
    ->   , ('6789012345', 'vid6')
    ->   , ('275293411', 'fixed');
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT address, title FROM amvideos WHERE address = `275293411`
    -> UNION
    -> SELECT address, title FROM amvideos WHERE address != `275293411` ORDER BY RANDOM() LIMIT 5;
ERROR 1054 (42S22): Unknown column '275293411' in 'where clause'
mysql> DROP TABLE `amvideos`;
Query OK, 0 rows affected (0.21 sec)

instead of a PHP false, I get a MySQL unknown column

This suggests that the backticks around the values are being considered as indicating a field identifier.

changing them to single quotes errors
ERROR 1305 (42000): FUNCTION RANDOM does not exist

changing it to RAND “works” (no errors) but not as I want it to

mysql> SELECT address, title FROM amvideos WHERE address = '275293411'
    -> UNION
    -> SELECT address, title FROM amvideos WHERE address != '275293411' ORDER BY RAND() LIMIT 5;
+------------+-------+
| address    | title |
+------------+-------+
| 1234567890 | vid1  |
| 4567890123 | vid4  |
| 5678901234 | vid5  |
| 275293411  | fixed |
| 2345678901 | vid2  |
+------------+-------+
5 rows in set (0.17 sec)

!! Don’t run the DROP TABLE on your real table !!

1 Like
11

SQLite has a similar tool - sqlite3.exe

sqlite> CREATE TABLE amvideos(address TEXT, title TEXT);
sqlite> INSERT INTO amvideos(address, title)
   ...> VALUES('1234567890', 'vid1')
   ...>   , ('2345678901', 'vid2')
   ...>   , ('3456789012', 'vid3')
   ...>   , ('4567890123', 'vid4')
   ...>   , ('5678901234', 'vid5')
   ...>   , ('6789012345', 'vid6')
   ...>   , ('275293411', 'fixed');
sqlite> SELECT address, title FROM amvideos WHERE address = `275293411`
   ...> UNION
   ...> SELECT address, title FROM amvideos WHERE address != `275293411` ORDER BY RANDOM() LIMIT 5;
Error: no such column: 275293411
sqlite> DROP TABLE amvideos;

instead of “unknown column” it gives “no such column” (meh, same difference)

changing the backticks to quotes gives
Error: 1st ORDER BY term does not match any column in the result set

changing to a single SELECT doesn’t error

sqlite> SELECT address, title FROM amvideos WHERE address != '275293411' ORDER BY RANDOM() LIMIT 5;
2345678901|vid2
5678901234|vid5
4567890123|vid4
6789012345|vid6
1234567890|vid1

So a problem could be a syntax issue. Indeed, this doesn’t error

sqlite> SELECT address, title
   ...> FROM amvideos
   ...> WHERE address = '275293411'
   ...> OR address IN (SELECT address
   ...>               FROM amvideos
   ...>               WHERE address != '275293411'
   ...>               ORDER BY RANDOM() LIMIT 5);
2345678901|vid2
3456789012|vid3
4567890123|vid4
5678901234|vid5
6789012345|vid6
275293411|fixed

If “fixed” needs to be the first in the result set, it will likely be tricky to do with SQL alone. This works, but only because “fixed” is a digit less than the others

sqlite> SELECT address, title
   ...> FROM amvideos
   ...> WHERE address = '275293411'
   ...> OR address IN (SELECT address
   ...>               FROM amvideos
   ...>               WHERE address != '275293411'
   ...>               ORDER BY RANDOM() LIMIT 5)
   ...> ORDER BY CAST(address AS integer) ASC;
275293411|fixed
1234567890|vid1
2345678901|vid2
3456789012|vid3
5678901234|vid5
6789012345|vid6
1 Like
12

you’re not trying hard enough

3 Likes
13

Thanks @Mittineague. I have opted for the two query option for now. It isn’t very elegant but it does the job. I will have a play with these queries - I use DB Browser for SQLite. I have also added another column (sequence) which I needed for a query on another page where the sequence is not random. I’m not sure if this would help here or not.

14

By jove, I think I have it

SELECT address, title, sequence 
FROM amvideos 
WHERE sequence = 1
OR address IN (SELECT address
               FROM amvideos
               WHERE sequence > 1
               ORDER BY RANDOM() LIMIT 5)
ORDER BY sequence;

seems to do the job. Of course, adding a column to the table made a difference. :shifty:

1 Like
15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.