Simple queries - combine two in to one statement

Fatback

Hi all - I'm brand new to MySQL.

I'm simply trying to combine these two select statements in to one:

SELECT id, 
       imageType, 
       word 
FROM images 
ORDER BY lastVote 
LIMIT 0, 1

Combined with:

SELECT id, 
       imageType, 
       word 
FROM images 
WHERE id <> ' (result from first query)' 
ORDER BY RAND( ) 
LIMIT 0 , 1

Can someone please show me how to do this? Thanks so much.

-J

Derokorian

Try:

SELECT id,
       imageType,
       word
  FROM images
 WHERE id NOT IN (
       SELECT id
         FROM images
     ORDER BY lastVote
        LIMIT 1
       )
ORDER BY RAND()
  LIMIT 1

Fatback

Hmmm I appreciate the reply - I got the following message:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Also, I probably should of clarified I'm actually trying to get the results of each query from just one combined query (so two results total). I'll keep fooling around with it a bit, thanks again.

Derokorian

So you want the most recent as determined by lastVote and a random row? Is that right?

Fatback

Yes sir!

Fatback

A random row that is not a record from the first query.

Derokorian

You could just use a union:

(SELECT id, 
       imageType, 
       word 
FROM images 
ORDER BY lastVote 
LIMIT 0, 1)
UNION
(SELECT id, 
       imageType, 
       word 
FROM images 
WHERE lastVote <> (SELECT MAX(lastVote) FROM images)
ORDER BY RAND( ) 
LIMIT 0 , 1)

Fatback

Unfortunately I still only get 1 result. Don't worry to much about it, I don't want people spending too much time on this. Thanks again for your help!

Derokorian

I'm just sitting here bored to tears today... I'm one of 2 people in the office that didn't call off, and 90% of my days are usually end-user support.... So I'm getting paid to troll basically!

Derokorian

This is working for me:

( SELECT id,name,lastVote
    FROM images
  ORDER BY lastVote DESC
   LIMIT 1)
UNION
( SELECT id,name,lastVote
    FROM images
   WHERE lastVote NOT IN (SELECT MAX(lastVote) FROM images)
  ORDER BY RAND()
    LIMIT 1)

My table definition:

CREATE TABLE `images` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `lastVote` date DEFAULT NULL,
 `name` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

Fatback

Hmm that's strange - I still get one result:

mysql> ( SELECT id, imageType, word
    ->     FROM images
    ->   ORDER BY lastVote
    ->    LIMIT 0,1)
    -> UNION
    -> ( SELECT id, imageType, word
    ->     FROM images
    ->    WHERE lastVote NOT IN (SELECT MAX(lastVote) FROM images)
    ->   ORDER BY RAND()
    ->     LIMIT 0,1);
+----+-----------+------+
| id | imageType | word |
+----+-----------+------+
|  3 | jpg       | goat |
+----+-----------+------+
1 row in set (0.00 sec)

My table def:

mysql> describe images \G;
*************************** 1. row ***************************
  Field: id
   Type: int(12)
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: imageType
   Type: varchar(4)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: word
   Type: varchar(12)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: approved
   Type: char(1)
   Null: NO
    Key:
Default: n
  Extra:
*************************** 5. row ***************************
  Field: authorName
   Type: varchar(25)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: authorUsername
   Type: varchar(15)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 7. row ***************************
  Field: authorEmail
   Type: varchar(35)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 8. row ***************************
  Field: rank
   Type: int(4)
   Null: NO
    Key:
Default: 1500
  Extra:
*************************** 9. row ***************************
  Field: votes
   Type: int(12)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 10. row ***************************
  Field: lastVote
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra:
*************************** 11. row ***************************
  Field: wins
   Type: int(12)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 12. row ***************************
  Field: losses
   Type: int(12)
   Null: NO
    Key:
Default: 0
  Extra:
*************************** 13. row ***************************
  Field: submissionDate
   Type: timestamp
   Null: NO
    Key:
Default: 0000-00-00 00:00:00
  Extra:
*************************** 14. row ***************************
  Field: submitter
   Type: varchar(35)
   Null: NO
    Key:
Default: NULL
  Extra:
14 rows in set (0.01 sec)

Derokorian

Hmmm

mysql> use test;
Database changed
mysql> ( SELECT id,name,lastVote
    ->     FROM images
    -> ORDER BY lastVote DESC
    ->    LIMIT 1
    -> ) UNION
    -> ( SELECT id,name,lastVote
    ->     FROM images
    ->    WHERE lastVote NOT IN (SELECT MAX(lastVote) FROM images)
    -> ORDER BY RAND()
    ->    LIMIT 1
    -> );
+----+-------------+------------+
| id | name        | lastVote   |
+----+-------------+------------+
|  6 | birthday    | 2012-03-25 |
|  4 | fake images | 2012-03-07 |
+----+-------------+------------+
2 rows in set (0.00 sec)

mysql>

And the second row changes almost every time I run it... I don't think timestamp would be a problem... You do have different values for each row in the lastVote column yes?

Fatback

Well I did see one of the lastVote columns as the same, so I changed it. I then ran the query a couple times and got two different results each time (1 record, then 2 record).... strange!

mysql> ( SELECT id, imageType, word
    ->      FROM images
    ->    ORDER BY lastVote
    ->     LIMIT 0,1)
    ->  UNION
    ->  ( SELECT id, imageType, word
    ->      FROM images
    ->     WHERE lastVote NOT IN (SELECT MAX(lastVote) FROM images)
    ->    ORDER BY RAND()
    ->      LIMIT 0,1);
+----+-----------+--------+
| id | imageType | word   |
+----+-----------+--------+
|  3 | jpg       | goat   |
|  5 | jpg       | bowser |
+----+-----------+--------+
2 rows in set (0.00 sec)

mysql> ( SELECT id, imageType, word
    ->      FROM images
    ->    ORDER BY lastVote
    ->     LIMIT 0,1)
    ->  UNION
    ->  ( SELECT id, imageType, word
    ->      FROM images
    ->     WHERE lastVote NOT IN (SELECT MAX(lastVote) FROM images)
    ->    ORDER BY RAND()
    ->      LIMIT 0,1);
+----+-----------+------+
| id | imageType | word |
+----+-----------+------+
|  3 | jpg       | goat |
+----+-----------+------+
1 row in set (0.00 sec)