Help with SELECT statement

[nzt2001]

What I am trying to do doesn't seem that hard, but I can't figure it out.

I have a single table that I want to select each row and group by one field and order by another.

Example:
blogger | blog text | blog date

jim blah 11/11/07
anna blah 11/10/07
chris blah 11/09/07
chris blah 11/08/07
jim blah 11/07/07
anna blah 11/06/07


I want to group by the blogger and date descending, BUT ALSO sort by which blogger has the newest entry

My output should be this:
jim blah 11/11/07
jim blah 11/07/07
anna blah 11/10/07
anna blah 11/06/07
chris blah 11/09/07
chris blah 11/08/07

What is the best way to produce my desired results?

Thanks

 

[elsenorjose]

Can you show the SQL you've tried so far?

The most logical approach would be:

...group by blogger
order by blog date desc

Is there some reason that's not working?

 

[nzt2001]

I had tried your suggestion 'elsenorjose' but it only gives me one row per group.(see below)



the closest I have come to what I want is this:

SELECT blogger, blog_text, blog_date, max(blog_date) as max_date FROM blog group by blogger order by max_date desc

this gives me

jim blah 11/11/07
anna blah 11/10/07
chris blah 11/09/07

The data is in the right order, but it only returns one row per group.

 

[piti]

i don't know what version you are using but this should work - have not tested, so maybe there is some typo ...

SELECT blog.blogger, blog.blog_text, blog.blog_date FROM blog LEFT JOIN (SELECT blogger, MAX(blog_date) AS mdate FROM blog GROUP BY blogger) AS temp_blog ON blog.blogger = temp_blog.blogger ORDER BY temp_blog.mdate DESC, blog.blog_date DESC;

 

[nzt2001]

Thanks piti!!!! That did it!