Query for width and height, a record with each greater than the other in the same query?

I am trying to create a single query that will pull results from a table with the following requirements:

1) Query database table for 5 records.
2) At least one record with image height greater than image width.
3) At least one record with image width greater than image height.
4) Results must be ordered by newest records first (time)

How would this be done? Thanks!

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image` char(100) NOT NULL DEFAULT '',
  `image_width` smallint(4) unsigned NOT NULL DEFAULT '0',
  `image_height` smallint(4) unsigned NOT NULL DEFAULT '0',
  `time` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

E开发者_C百科DIT: Added time field and time order requirement. Records must be ordered by the time field (used an arbitrary int field for the example).

---

SELECT * FROM images WHERE id IN (
     SELECT id FROM images WHERE image_width > image_height ORDER BY RAND() LIMIT 1
     UNION
     SELECT id FROM images WHERE image_height > image_width ORDER BY RAND() LIMIT 1
     UNION
     SELECT id FROM images ORDER BY RAND() LIMIT 3
)

---

To expand on the other union attempts... The inner most query should return 7 records for possibility that even with random, you could STILL get the WIDTH and/or HEIGHT image. So, taking that inner most query and sorting by the extra column I added of "Required" = 1 or 2 will put the required ones first and secondary ones after. Then, the OUTER query will select DISTINCT elements from the inner query which SHOULD begin with natural order of inner query result of 1 Required, 1 Required, 2 Required, 2 Required, 2 Required, 2 Required, 2 Required. By doing distinct on the ID, if the first "2 required" is the same ID as width or height, it will immediately be ignored leaving the outer natural result set to 6 records... So the outer most LIMIT will then force truncating it down to the final 5 you were hoping for.

select images.*    
   from images,
        ( select distinct id
             from ( SELECT QryUnions.id, 
                           QryUnions.required
                       FROM ((select imgW.id, 
                                     1 Required,
                                     rand() rand1
                                from images imgW
                                where imgW.image_width > imgW.image_height
                                group by 1
                                order by 3
                                limit 1)
                             union all
                             (select imgH.id, 
                                     1 Required,
                                     rand() rand1
                                from images imgH
                                where imgH.image_height > imgH.image_width
                                group by 1
                                order by 3
                                limit 1)
                             union all
                             (select imgR.id, 
                                    2 Required,
                                    rand() rand1
                                from images imgR
                                group by 1
                                order by 3
                                limit 5)) QryUnions
                        order By
                          QryUnions.Required ) PreQualify
            order by
                Required 
            limit 5 ) Final5
    where
       images.id = Final5.id

---

SELECT * FROM images WHERE image_width > image_height LIMIT 1
UNION
SELECT * FROM images WHERE image_height > image_width LIMIT 1
UNION
(SELECT * FROM images LIMIT 3);

To avoid row duplication you can use the following:

SELECT DISTINCT * FROM (
    SELECT * FROM images WHERE image_width > image_height LIMIT 1
    UNION
    SELECT * FROM images WHERE image_height > image_width LIMIT 1
    UNION
    (SELECT * FROM images LIMIT 3)
) AS i;

---

SELECT DISTINCT id FROM (
    SELECT *, 'A' AS ord FROM images WHERE image_width > image_height LIMIT 1
    UNION
    SELECT *, 'B' AS ord FROM images WHERE image_height > image_width LIMIT 1
    UNION
    (SELECT *, 'C' AS ord FROM images)
) AS i ORDER BY ord LIMIT 5;

If you want to retrieve the full rows you can do the following instead:

SELECT * FROM images WHERE id IN (
    SELECT id FROM (
        SELECT *, 'A' AS ord FROM images WHERE image_width > image_height LIMIT 1
        UNION
        SELECT *, 'B' AS ord FROM images WHERE image_height > image_width LIMIT 1
        UNION
        (SELECT *, 'C' AS ord FROM images LIMIT 7)
    ) AS i ORDER BY ord
) LIMIT 5;