CODEWITHSUNDEEP
mysqlsql
Dexy_Wolf
Dexy_Wolf

Reputation: 999

Problem with sorting comments in date order

I am novice in MySQL and I have a problem with sorting two tables.

This SQL is about sorting newest comments on books, but I am getting these books sorted by first comments on them, not on latest.

SELECT b.*, c.date_added as date FROM books b
        LEFT JOIN comments c ON (b.id = c.book_id)
        GROUP BY b.id
        ORDER BY date DESC 
        LIMIT 5

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) COLLATE utf8_bin NOT NULL,
  `author` varchar(255) COLLATE utf8_bin NOT NULL,
  `pages` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `filename` varchar(255) COLLATE utf8_bin NOT NULL,
  `description` text COLLATE utf8_bin NOT NULL,
  `date_added` datetime NOT NULL,
  `publisher` varchar(255) COLLATE utf8_bin NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `times_sold` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) NOT NULL,
  `author` varchar(255) COLLATE utf8_bin NOT NULL,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `body` text COLLATE utf8_bin NOT NULL,
  `date_added` datetime NOT NULL,
  `approved` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Thank you for your time.

Upvotes: 0

Views: 110

Answers (3)

Anonymous
Anonymous

Reputation: 3689

There's no field called 'date', you need to order it by date_added (ORDER BY date_added DESC) as it is in your table! If it's still not in the right order, just use ASC instead of DESC.

Upvotes: 0

Hugh Jones
Hugh Jones

Reputation: 2694

The 'Group By' clause is not correct and you need to remove it.

'Group By' is used for aggregate functions like Min() and Max().

Try This

SELECT b.*, c.date_added FROM books b
        JOIN comments c ON  
          c.book_id = b.id
        ORDER BY c.date_added DESC 
        LIMIT 5

Upvotes: 0

Kibbee
Kibbee

Reputation: 66132

This should work out. You are specifying the group on b.id, but since you are multiple comments for each book, you need an aggregate function on c.date_added. In this case you can use MAX to show the most recent comment date.

    SELECT b.*, MAX(c.date_added) as date FROM books b
    LEFT JOIN comments c ON (b.id = c.book_id)
    GROUP BY b.id
    ORDER BY MAX(c.date_added) DESC 
    LIMIT 5

Upvotes: 2

Related Questions