Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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).

like image 979
Highway of Life Avatar asked Feb 15 '11 16:02

Highway of Life


1 Answers

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
)
like image 73
jewel Avatar answered Oct 20 '22 05:10

jewel