greenday Posted April 1, 2008 Share Posted April 1, 2008 Hi, I am getting results from 2 tables using the group by method. The reason for 2 tables is one contains all the news details and categoryid, and the other contains the categoryid and categoryid title. Here is the code SELECT articles.articlesummary, articles.articleid, articles.articlecategoryid, articlecategory.articlecategoryname FROM articles, articlecategory WHERE articles.articlecategoryid = articlecategory.articlecategoryid GROUP BY articlecategory.articlecategoryname ORDER BY articles.articlecategoryid ASC LIMIT 0 , 30 Works great, just as i want, except that its getting the first(oldest) articleid listing from the table for each article. I want the last(newest) articleid listing for each. Any help please? Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/ Share on other sites More sharing options...
quiettech Posted April 1, 2008 Share Posted April 1, 2008 ... ORDER BY articles.articlecategoryid ASC, articles.articleid DESC ... Is that it? I'd probably also suggest changing the group by clause to articles.articlecategoryid Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-506133 Share on other sites More sharing options...
greenday Posted April 1, 2008 Author Share Posted April 1, 2008 thanks, but that doesnt seem to work, even when i group by articles.articlecategoryid as suggested. I think the problem is that any ordering happens after the results have been got, so its only ordering the same results differantly. Where as I am needing to get differant results in the first place. Maybe my topic title was wrong. Any other ideas? Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-506141 Share on other sites More sharing options...
quiettech Posted April 1, 2008 Share Posted April 1, 2008 Ah, I see. Well, since you are not using an aggregate, I doubt you need the group by clause. You see, it automatically sorts your results on that field unless you add "ORDER BY NULL" to it. And yes, it does this before ORDER BY is evaluated. So, my suggestions is to remove the group by clause and instead: ORDER BY articlecategory.articlecategoryname, articles.articleid DESC Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-506163 Share on other sites More sharing options...
greenday Posted April 1, 2008 Author Share Posted April 1, 2008 thanks for your suggestion, but this gives me the entire records. I used group by as I want just one articlesummary for each articlecategoryid. Can you think of another way? Cheers. Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-506176 Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 Ok, so you're looking to select the "latest" (which you define how? datetime? id?) article with it's category name? Sounds a bit more like this: (until you give me full table description) SELECT a.articlesummary ,a.articleid ,ac.articlecategoryname FROM articles AS a JOIN articlecategory AS ac ON a.articlecategoryid = ac.articlecategoryid WHERE id = (SELECT MAX(articleid) FROM articles) Your DDL (data definition language) will help clear up any inconsitencies in the above... Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-506277 Share on other sites More sharing options...
greenday Posted April 2, 2008 Author Share Posted April 2, 2008 aschk, thanks alot for your help, your sql statement gives me 1 record exactaly how i need, by selecting the highest articleid gives me the latest. However, I am looking to get the 'latest' article that is in each articlecategoriy. My origional group by done this, but it used the first results 'oldest' and lowest articleid. Any ideas? maybe i need to somehow combine the group by and the join? cheers. Quote Link to comment https://forums.phpfreaks.com/topic/98904-group-by-ordering-help/#findComment-507063 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.