distinct/group by question

sickofit

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

ID(autoincr) | ITEM(parent-relation to items table) | FROM(relation to USERID table) |TIME(unixtimestamp)

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

SELECT comments.item, users.username, items.title
FROM comments
LEFT JOIN users ON comments.from = users.userid
LEFT JOIN items ON comments.item = items.itemid
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.item, users.username, items.title
FROM comments
LEFT JOIN users ON comments.from = users.userid
LEFT JOIN items ON comments.item = items.itemid
GROUP BY comments.item
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)

thanks in advance!!

(added DESC in the example.. works the same)

bogu

Then why dont u make your order DESC
ORDER BY comments.time DESC

bogu

Then why dont u make your order DESC?

ORDER BY comments.time DESC

sickofit

oh i am sorry, im already doing it.
but i forgot to write it in the example.

doesnt make a difference.

bogu

It should if the timestamp is different, u should check your records and see if the timestamp is different ...

sickofit

yes the timestamp in the comments table are not the same (its unix time).
i got like 30 comments already.

the problem is.. the GROUP BY just get the FIRST comment, i need the LAST

the ORDER BY just orders the full row.

i need like... "GROUP BY timestamp DESC" lol

bogu

sickofit wrote:
GROUP BY just get the FIRST comment, i need the LAST

GROUP BY have nothing to do with your FIRST or YOUR las records, u should recheck your code something is fishy there, just try a few examples of GROUP BY and ORDER BY or add to this thread your sql file of your tables and there records, I'm to lazy to create them and populate them ... :p

sickofit

well this isnt the actual db im using, just a clone showing the stuff implicated in this problem..
(i cant post the original is waaaay to many "crap" info)

-- 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=InnoDB 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=InnoDB 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=InnoDB 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');

sickofit

the query will be like 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

this give me:

commentid title username
1 title of content 1 bob
2 title of content 2 bob

but is wrong, sandra commented last!

btw i updated the sql, copy it again please (i simplified the time, just 10-11-12-13 seconds)

bogu

Why do u GROUP BY item id?

sickofit

because, like i said, i dont want duplicated items on the while..
i could GROUP BY title, the results will be the same.

bogu

sickofit wrote:
because, like i said, i dont want duplicated items on the while..
i could GROUP BY title, the results will be the same.

Ofcource is not working because the select is executed and then the ORDER BY is apply to it ...

I cant see a solution for doing this direct from one SELECT, but I'm not a mysql guru; I'll think to a select, if I find a solution I'll posted ...

sickofit

yeap.. mysql selects all the comments right..
then it GROUPS them, but sadly this time it grabs the FIRST row in the DB
i want it to GROUP, and then return just the LAST row in the DB..

then the ORDER is applied, to all the grouped items.. but its worthless.
like i said, i need ORDER before GROUP hehe

thanks for the help bogus ill keep looking for a form of doing this directly in 1 query.

sickofit

i saw this on the mysql official page.. do you know how to apply it here?

If you want to use ORDER BY before GROUP BY, the only way I've found to achieve it is with a subquery.

For example, if you want to get a list of users from a table UserActions sorted according to the most recent action (based on a field called Time) the query would be:

SELECT FROM (SELECT FROM UserActions ORDER BY Time DESC) AS Actions GROUP BY UserID ORDER BY Time DESC;

sickofit

k i did it. this is the "final query" obviusly i need to put some limits on it...

$sql = "
SELECT * FROM 
(SELECT comments.itemid as comid, comments.tstamp, items.titulo, comments.fromid, items.itemid, items.standing
FROM comments
LEFT JOIN items ON comments.itemid = items.itemid
ORDER BY comments.tstamp DESC) 
AS coms GROUP BY titulo ORDER BY tstamp DESC;
";