Group By Ordering Help

[greenday]

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.

[quiettech]

...
ORDER BY articles.articlecategoryid ASC, articles.articleid DESC
...

 

Is that it?

 

I'd probably also suggest changing the group by clause to articles.articlecategoryid

[greenday]

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?

[quiettech]

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

 

 

 

[greenday]

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.

[aschk]

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...

[greenday]

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.