Jump to content

distinct/group by/desc problem


sickofit

Recommended Posts

hi! new to this forums...
ill be glad if some of you guys can help me with this:

this is the scenario.. i got a comment table for a blog right..

name of comments table is comments, name of users table is users, name of content table is items

ok so i want to query the LAST 5 CONTENTS COMMENTED.
simple as this:

SELECT comments.commentid, content.title, users.username
FROM comments
LEFT JOIN content ON comments.itemid = content.itemid
LEFT JOIN users ON comments.fromid = users.userid
ORDER BY comments.time DESC
LIMIT 5

ok so this works well, i have a result like this in the WHILE:

ITEM: 2 (for the link to the content)
USERNAME: bob (this is the author of the LAST comment, thanks to the ORDER BY)
TITLE: sample stuff (the title of the content commented)

OK SO NOW i need to have UNIQUE titles right, with the LAST COMMENT author.

so i try this:

SELECT comments.commentid, content.title, users.username
FROM comments
LEFT JOIN content ON comments.itemid = content.itemid
LEFT JOIN users ON comments.fromid = users.userid
GROUP BY comments.itemid
ORDER BY comments.time DESC
LIMIT 5

now i have unique items, BUT the last comment author isnt the last... its the first
so in "simple but wrong" words i need to "SORT" the GROUP BY.. i need to pick the LAST comment (based on TIME (unixstamp) value of each row).

mysql stops when it finds a unique row on items.. but that row is the FIRST comment, i need the LAST.

i already tried DISTINCT, works the same

this will be wonderful if ORDER BY could come BEFORE GROUP BY

is there a way to do this? (directly in mysql, i know how to do it in php but it feels like.. messy)

this is the dump of a sample db, using the same conditions:

-- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 04, 2006 at 01:23 PM
-- Server version: 5.0.16
-- PHP Version: 5.1.1
--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
`commentid` int(11) NOT NULL auto_increment,
`fromid` int(11) NOT NULL,
`itemid` int(11) NOT NULL,
`time` int(11) NOT NULL,
`body` varchar(50) NOT NULL,
PRIMARY KEY (`commentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`commentid`, `fromid`, `itemid`, `time`, `body`) VALUES (1, 1, 1, 10, 'this is my comment'),
(2, 1, 2, 11, 'this is my other comment'),
(3, 2, 1, 12, 'sandras comment'),
(4, 1, 2, 13, 'sandras comment another');

-- --------------------------------------------------------

--
-- Table structure for table `content`
--

CREATE TABLE `content` (
`itemid` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`body` varchar(100) NOT NULL,
PRIMARY KEY (`itemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `content`
--

INSERT INTO `content` (`itemid`, `title`, `body`) VALUES (1, 'title of content 1', 'body of content 1'),
(2, 'title of content 2', 'body of content 2');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
`userid` int(11) NOT NULL auto_increment,
`username` varchar(12) NOT NULL,
`email` varchar(35) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userid`, `username`, `email`) VALUES (1, 'bob', 'bob@bob.com'),
(2, 'sandra', 'sandra@sandra.com');

thanks in advance

thanks in advance!!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.