ordering MySQL query results 1

[zparticle]

Basically what I'm trying to do is get a list of picture rows, ordered by the number of comments each picture has received in descending order, for a given time period. My query doesn't bring them back in the right order and I have no idea how to fix it.

//------------------
select * from pictures
where id in
(
select id
from
(
SELECT count(*) as c,pictures_id as id
FROM comments
where posted_on>=SUBDATE(CURRENT_DATE, INTERVAL #hours# HOUR)
group by pictures_id
order by c desc
)
as x
)
and processed=1
limit #start#,#count#
//------------------

hours is the number of hours prior to the current time to include, start and count are being used for paging purposes.

The tables are:

CREATE TABLE IF NOT EXISTS `foo`.`pictures` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`folders_id` INT NULL ,
`title` VARCHAR(45) NULL ,
`users_id` INT UNSIGNED NOT NULL ,
`location` VARCHAR(75) NULL ,
`details` VARCHAR(1000) NULL ,
`submitted_on` DATETIME NULL ,
`uploaded_on` DATETIME NULL ,
`orig_filename` VARCHAR(45) NOT NULL ,
`processed` INT NOT NULL ,
`storage_location` VARCHAR(200) NULL ,
`tag_words` VARCHAR(100) NULL ,
`exif_text` VARCHAR(4096) NULL ,
`view_count` INT UNSIGNED NULL ,
PRIMARY KEY (`id`) ,
CONSTRAINT `fk_pictures_folders`
FOREIGN KEY (`folders_id` )
REFERENCES `foo`.`folders` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `fk_pictures_users`
FOREIGN KEY (`users_id` )
REFERENCES `foo`.`users` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


CREATE TABLE IF NOT EXISTS `foo`.`comments` (
`id` INT NOT NULL AUTO_INCREMENT ,
`comment` VARCHAR(1000) NOT NULL ,
`pictures_id` INT UNSIGNED NOT NULL ,
`posted_on` DATETIME NOT NULL ,
`users_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`id`) ,
CONSTRAINT `fk_comments_pictures`
FOREIGN KEY (`pictures_id` )
REFERENCES `foo`.`pictures` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `fk_comments_users`
FOREIGN KEY (`users_id` )
REFERENCES `foo`.`users` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

 

[AdaHacker]

You can't do the ordering of the result set in a subquery. SQL doesn't work that way. SQL is set-based and sets are inherently without order. In fact, it might help you to remember that the ORDER BY clause isn't even part of the query per se, but really belongs to an implicit cursor declaration.

A better way to do this would be to use a join rather than an IN clause. This allows you to get at the count to order on it. It also eliminates that nested subselect, which isn't really necessary. For example:

SELECT p.*, c.cnt 
FROM pictures AS p JOIN 
    (SELECT COUNT(*) as cnt, picture_id 
     FROM comments 
     WHERE posted_on>=SUBDATE(CURRENT_DATE, INTERVAL #hours# HOUR) 
     GROUP BY picture_id) AS c 
    ON p.id = c.picture_id
WHERE processed=1
ORDER BY cnt DESC
LIMIT #start#, #count#;

 

[zparticle]

Thank you very much! That does exactly what I wanted. Now I'll have a clue as to how to do this in the future.