[SOLVED] Distinct join sorting

[tradet]

Hello, I'm having trouble while creating my own blogging page.

 

I have a table called blog

CREATE TABLE `blog` (\n  `id` int(10) unsigned NOT NULL auto_increment,\n  `title` varchar(200) NOT NULL,\n  `entry` text NOT NULL,\n  `date_entered` datetime NOT NULL,\n  PRIMARY KEY  (`id`),\n  FULLTEXT KEY `full` (`entry`,`title`)\n) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=latin1

 

and one called comments

CREATE TABLE `comments` (\n  `id` int(10) unsigned NOT NULL auto_increment,\n  `blog_id` int(10) unsigned NOT NULL,\n  `ip` varchar(20) NOT NULL,\n  `date_entered` datetime NOT NULL,\n  `user_id` int(10) unsigned NOT NULL,\n  `entry` text NOT NULL,\n  `name` varchar(45) NOT NULL,\n  `link` varchar(45) default NULL,\n  `approved` tinyint(1) NOT NULL,\n  PRIMARY KEY  (`id`)\n) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1

 

Short thing: I've got a 'blog_id' in comments which connects with the 'id' in blog, to get what comment is to what post.

 

I want to get id (or blog_id from comment) and title from blog where comments.blog_id = blog.id and I want to sort by comments.date_entered.

 

 SELECT DISTINCT comments.blog_id, blog.title
FROM comments
JOIN blog
WHERE comments.blog_id = blog.id
AND comments.approved = '1'
ORDER BY comments.date_entered DESC
LIMIT 6 

 

Forgot that I also want comments.approved = '1'.

 

Everything works except it's not sorted and I can't get it to sort whatever I do.

 

I'm newish so take it slow, appreciate all help.

[Barand]

The syntax is

 

FROM a JOIN b ON a.x = b.y WHERE something

[tradet]

Okay

SELECT DISTINCT comments.blog_id, blog.title
FROM comments
JOIN blog ON comments.blog_id = blog.id
WHERE comments.approved = '1'
ORDER BY comments.date_entered DESC
LIMIT 6

Still doesn't sort it right.

 

That query

blog_id 
49
46
37
45
34
33 

 

Correct (should be)

blog_id 
49 
33 
37 	
46
37
45 

[Barand]

how is your date stored

[tradet]

As datetime

ex.

2008-10-07 18:41:14

[Barand]

include the date in the selection and post the output. Maybe we can spot what's happening

[tradet]

Some testing:

SELECT DISTINCT comments.blog_id, blog.title
FROM comments
JOIN blog ON comments.blog_id = blog.id
WHERE comments.approved = '1'
ORDER BY comments.date_entered DESC
LIMIT 6 

outputs

blog_id 	title
49 	Flashing neo freerunner
46 	L
37 	How to become a good programmer?
45 	The breast month
34 	New author
33 	Open source and the masses

Problem here: not sorted

 

SELECT DISTINCT comments.blog_id, comments.date_entered, blog.title
FROM comments
JOIN blog ON comments.blog_id = blog.id
WHERE comments.approved = '1'
ORDER BY comments.date_entered DESC
LIMIT 6 

outputs

blog_id 	date_entered 	title
49 	2008-10-07 18:41:14 	Flashing neo freerunner
33 	2008-10-07 00:52:26 	Open source and the masses
37 	2008-10-07 00:48:33 	How to become a good programmer?
46 	2008-10-05 19:45:16 	L
37 	2008-10-01 21:39:36 	How to become a good programmer?
45 	2008-10-01 21:24:07 	The breast month

Problem here: blog_id not unique

[Barand]

It's sorting ok by date.

 

You wouldn't expect the blogid to be unique when there are multiple comments per blog

[tradet]

No but I'm trying to make a "Latest comment" thing on my site. Where it displays the title and a link to a post with the latest comment. And it doesn't matter if the 5 latest comments are in post A, it should still display A then B then C.

 

Edit: Like on codinghorror: http://www.codinghorror.com/blog/, "Last comments"

[Barand]

Do you want the latest comments (which you are getting now) or the six blogs with the latest comments?

[tradet]

The six blogs with the latest comments.

[Barand]

SELECT comments.blog_id, MAX(comments.date_entered) as latest, blog.title

FROM comments

JOIN blog ON comments.blog_id = blog.id

WHERE comments.approved = '1'

GROUP BY comments.blog_id

ORDER BY latest DESC

LIMIT 6

[tradet]

Whoa! Thanks alot 

 

I don't really understand the MAX thing, it sorts it and stores it as 'latest' but not 100% on how it works.

You use GROUP BY instead of DISTINCT; it will take the max date_entered value when it groups?

[Barand]

The GROUP BY and MAX work together.

 

They get the latest comment date for each blog'.

[tradet]

ok...  :-X

 

Thanks again!