Ordering by a different table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Ordering by a different table?

Post by Smasher »

Code: Select all

	SELECT t.id FROM `Thread` t 
			JOIN `Post` p ON (p.thread_id = t.id)
			WHERE t.`forum_id` = $id 
			GROUP BY p.`thread_id` 
			ORDER BY p.`updated` DESC
Basically, Im returning a list of threads per forum via a query, however the list of threads should be returned by the post updated time.

However it seems this query just returns the thread ids either asc or desc and doesnt use the p.updated asc/desc

Anyone have an idea of how I'm doing this wrong?

edit, technically I'd need two orders, one order to order the posts and then another to order the thread, however they'd both be the updated field
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Try removing the GROUP BY clause.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Makes no difference :s
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

From the look of your query, you're just... Doing it wrong. There's multiple posts per thread, right? Your join accounts for the first post for each thread, when you probably want the last post.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Thats right, thats what I did the orderby updated bit.

I know I'm doing it wrong, I just cant figure out whats the right way todo it.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Do you have an advise on how todo it right?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Okay, I think this is what you're wanting:

Code: Select all

SELECT t.id
FROM thread AS t, post AS p
WHERE p.thread_id = t.id
GROUP BY p.thread_id
ORDER BY p.updated DESC
That *should* give you the thread IDs in reverse chronological order (newest first). If that's not right, trying changing the group by to t.id instead of p.thread_id.

Hope it helps!
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

hmm it still ordered by the thread id :/
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

okay, what did you get when you pull out the "group by p.thread_id"?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Can you post the table structures (CREATE TABLE ....) some sample data and the expect output for said data?
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[quote="ReverendDexter"]okay, what did you get when you pull out the "group by p.thread_id"?[/quote]

The same


[quote="feyd"]Can you post the table structures (CREATE TABLE ....) some sample data and the expect output for said data?[/quote]

Sure, here

[syntax="sql"]CREATE TABLE `Post` (
  `id` int(8) NOT NULL auto_increment,
  `post` text NOT NULL,
  `user_id` int(8) NOT NULL default '0',
  `thread_id` int(8) NOT NULL default '0',
  `smilies` int(1) NOT NULL default '0',
  `ip` varchar(30) NOT NULL default '',
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

-- 
-- Dumping data for table `Post`
-- 

INSERT INTO `Post` VALUES (1, 'This is the first post.', 1, 1, 1, '81.179.79.90', '2007-09-04 22:20:38', '2007-09-04 22:20:38');
INSERT INTO `Post` VALUES (2, 'This is the second post.', 7, 1, 1, '86.131.249.69', '2007-09-05 16:05:35', '2007-09-05 16:05:35');
INSERT INTO `Post` VALUES (3, 'testers', 1, 10, 1, '86.20.130.212', '2007-11-30 16:52:59', '2007-09-30 16:52:22');
INSERT INTO `Post` VALUES (4, 'That ring tone innit', 1, 11, 1, '86.20.130.212', '2007-09-30 19:13:22', '2007-09-30 19:13:22');
INSERT INTO `Post` VALUES (5, 'Special Ring UP!', 1, 11, 1, '86.20.130.212', '2007-09-30 19:39:49', '2007-09-30 19:39:49');
INSERT INTO `Post` VALUES (6, 'Tester Up!', 1, 2, 1, '86.20.130.212', '2007-09-30 19:40:23', '2007-09-30 19:40:23');
INSERT INTO `Post` VALUES (7, 'Why am I not up? ', 1, 11, 1, '86.20.130.212', '2007-09-30 19:40:35', '2007-09-30 19:40:35');
INSERT INTO `Post` VALUES (23, 'rfgrftg', 1, 10, 1, '86.20.130.212', '2007-10-04 18:19:05', '2007-10-04 18:19:05');
INSERT INTO `Post` VALUES (22, 'make me1', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:55', '2007-10-04 18:18:55');
INSERT INTO `Post` VALUES (21, 'godamit', 1, 10, 1, '86.20.130.212', '2007-10-04 18:18:20', '2007-10-04 18:18:20');
INSERT INTO `Post` VALUES (20, 'im ere', 1, 11, 1, '86.20.130.212', '2007-10-04 18:18:05', '2007-10-04 18:18:05');
INSERT INTO `Post` VALUES (19, 'im here', 1, 11, 1, '86.20.130.212', '2007-10-04 18:17:59', '2007-10-04 18:17:59');
INSERT INTO `Post` VALUES (18, 'Im new', 1, 11, 1, '86.20.130.212', '2007-09-30 21:34:38', '2007-09-30 21:34:38');
INSERT INTO `Post` VALUES (17, 'TEster should move up', 1, 10, 1, '86.20.130.212', '2007-09-30 21:32:54', '2007-09-30 21:32:54');


-- 
-- Table structure for table `Thread`
-- 

CREATE TABLE `Thread` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `forum_id` int(10) NOT NULL default '0',
  `icon_id` int(8) NOT NULL default '0',
  `views` int(8) NOT NULL default '0',
  `posts` int(8) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- 
-- Dumping data for table `Thread`
-- 

INSERT INTO `Thread` VALUES (1, 'Pete Burns', 1, 1, 233, 41);
INSERT INTO `Thread` VALUES (2, 'Dorethy', 1, 2, 18, 55);
INSERT INTO `Thread` VALUES (10, 'tester', 3, 1, 40, 9);
INSERT INTO `Thread` VALUES (11, 'Special Ring', 3, 1, 33, 12);


-- 
-- Table structure for table `Forum`
-- 

CREATE TABLE `Forum` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  `icon` int(11) NOT NULL default '0',
  `category_id` int(8) NOT NULL default '0',
  `order` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `Forum`
-- 

INSERT INTO `Forum` VALUES (1, 'General Discussion', 'A place to shoot from the hip', 1, 2, 1);
INSERT INTO `Forum` VALUES (2, 'Off Topic Banter', 'A place to spam away like there is no tomorrow.', 1, 2, 2);
INSERT INTO `Forum` VALUES (3, 'Forum Discussion', 'A place to discuss Wuggawoo and its development.', 3, 1, 2);
INSERT INTO `Forum` VALUES (4, 'News Discussion', 'A place to discuss serious news articles.', 1, 1, 1);

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What about the expected output of the final query? (Or an approximation, as best you can mock up.)
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

http://wuggawoo.co.uk/betaForum/?page=f ... =view&id=3

At the moment it orders by the id asc or desc *depending whats put at the end of the query* whereas it should order by the last post.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Did anyone look into this? I could provide some code if it'd help test.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

Bumpen :)
Post Reply