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`)
)
EDIT: 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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With