[eluser]Dan Murfitt[/eluser]
[quote author="ehicks727" date="1219776513"]ah, I take that back, yes you can use ORDER BY with a JOIN. Maybe you need to specify the JOIN, like a LEFT JOIN or something like that. I didn't really read the question closely enough... haven't had my coffee yet this morning  [/quote]
Thanks for your help I think it was the group by which I am using incorrectly, as I'm using it to only show one result for each conversation. Here's an SQL dump from phpmyadmin which contains some sample data. This contains 4 conversations, which were created in order as their topic will suggest, but conversationid 2 has 2 posts. The second post in conversationid 2 was the last to be updated, so it should appear at the top of the resultset making the order 2, 4, 3, 1 (in accordance with the conversations_posts table, ordered by the created field).
If I only created a join (but no group by) there would be 2 results for conversationid 2, as there are two posts - so to limit this to 1 result per conversation I group the conversationid. Here's the SQL:
Code: -- phpMyAdmin SQL Dump
-- version 2.11.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 26, 2008 at 02:57 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `temp`
--
-- --------------------------------------------------------
--
-- Table structure for table `conversations`
--
CREATE TABLE IF NOT EXISTS `conversations` (
`conversationid` int(11) NOT NULL auto_increment,
`teamid` int(11) NOT NULL,
`userid` int(11) NOT NULL,
`created` int(11) NOT NULL,
`topic` varchar(255) NOT NULL,
PRIMARY KEY (`conversationid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;
--
-- Dumping data for table `conversations`
--
INSERT INTO `conversations` (`conversationid`, `teamid`, `userid`, `created`, `topic`) VALUES
(4, 1, 1, 6, 'Last conversation'),
(3, 1, 1, 5, 'Third conversation'),
(2, 1, 1, 4, 'Second conversation'),
(1, 1, 1, 3, 'First conversation');
-- --------------------------------------------------------
--
-- Table structure for table `conversations_posts`
--
CREATE TABLE IF NOT EXISTS `conversations_posts` (
`postid` int(11) NOT NULL auto_increment,
`conversationid` int(11) NOT NULL,
`userid` int(11) NOT NULL,
`created` int(11) NOT NULL,
`message` longtext NOT NULL,
PRIMARY KEY (`postid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=108 ;
--
-- Dumping data for table `conversations_posts`
--
INSERT INTO `conversations_posts` (`postid`, `conversationid`, `userid`, `created`, `message`) VALUES
(1, 1, 1, 3, 'Fusce et nulla eu arcu vehicula mollis. Aliquam leo. Curabitur convallis. Vestibulum tellus. Nulla odio ligula, eleifend quis, sollicitudin vehicula, vestibulum eu, sapien. Nunc ligula eros, euismod venenatis, auctor at, ultrices vel, elit. Maecenas feugiat arcu id lorem. Donec sodales consectetuer augue. Duis tortor ipsum, faucibus in, gravida sed, mattis sit amet, elit. Cras ut libero!'),
(2, 2, 1, 4, 'Cras in dolor in dolor mollis lobortis? Proin urna pede, convallis eu, mattis vehicula, fringilla et, lectus! Mauris a dolor in augue vehicula vulputate. Mauris rutrum, dui eu sodales eleifend, arcu diam vehicula ante, at pulvinar risus ipsum ac justo. Phasellus eget turpis? Aliquam vel magna ac sapien sagittis cursus. Nunc sit amet est vitae ligula ullamcorper congue. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Phasellus vel mauris a nisl fringilla semper. Vivamus convallis mattis tortor. Aenean dolor lacus, interdum a, adipiscing a; pellentesque et, ipsum. Ut id urna vel metus imperdiet vehicula. Aenean ullamcorper elit non nulla. Morbi in pede ac libero malesuada eleifend!'),
(3, 3, 1, 5, 'Nullam dictum felis eu ligula. Integer faucibus, orci at accumsan ultricies, ipsum leo adipiscing dolor, vel laoreet diam nulla eu tortor? Integer eget est! Aliquam consequat quam sed justo. Nunc ut est. Phasellus purus velit, ultricies sed, sagittis vel, ultrices vitae, nunc? Duis pharetra felis quis libero. Suspendisse lobortis neque at massa. Proin eleifend imperdiet mauris? Ut tristique ante eget lorem. Suspendisse potenti. Sed non pede! Phasellus non turpis.'),
(4, 4, 1, 6, 'Nunc vehicula velit a arcu. Curabitur turpis diam, facilisis at; commodo quis, dapibus sed, elit. Pellentesque mattis! Maecenas purus enim; euismod et, vestibulum vitae, adipiscing in, sem! Suspendisse potenti. Maecenas massa. Maecenas pharetra, elit id venenatis varius, libero leo molestie dolor, sed aliquet urna nisl vel magna. Praesent auctor rutrum eros. Nulla felis neque; scelerisque cursus, ullamcorper ut, porttitor in, pede. Curabitur sem eros, pulvinar nec, vehicula vitae, sodales a; nulla.'),
(5, 2, 1, 7, 'Quisque viverra tellus non metus. Etiam ornare neque quis odio. Integer at nisl eu est sollicitudin vestibulum. Donec eget mi. Curabitur interdum leo non nibh. Proin pretium. Aliquam pulvinar vehicula diam. Fusce libero odio, dignissim eget, facilisis sit amet, rutrum vel, est. Pellentesque ligula felis, rutrum non, scelerisque sit amet, congue at, risus. Nullam diam ipsum, sodales at, tempus vitae, vulputate vitae, erat.');
Thanks again!
|
