SELECT and GROUP BY

[Chewy509]

Warning: SQL noobie...

I'm trying to create a query where the most recent entry for each user is returned from a forum table, and sorted by username. The fields are simply: user_id, username, post, last_updated. No primary key defined.

I've tried the simple SELECT * FROM posts GROUP BY user_id DESC ORDER BY username. It returns what I'm after, but not the lastest post from each user, (but the first post). Now my understanding of the GROUP BY function may not be correct, but according to the MySQL manual adding DESC after GROUP BY shold return what I'm after but doesn't. Any hints?

(Table type is InnoDB if that matters, running on MySQL 4.0.16).

 

[PROFESSORSPARKIE]

Did you try placing the sort sequence of “DESC” just after the key “ORDER BY” field name

You coded:

SELECT * FROM posts GROUP BY user_id DESC ORDER BY username.

Try coding :

Select * FROM POSTS GROUPED BY user_id ORDER BY username DESC;

If not try coding both.

 

[r937]

select t1.user_id
     , t1.username
     , t1.post
     , t1.last_updated
  from yourtable t1
inner
  join yourtable t2
    on t1.user_id = t2.user_id
   and t1.last_updated >= t2.last_updated
group
    by t1.user_id
     , t1.username
     , t1.post
     , t1.last_updated
having count(*) = 1

rudy

http://r937.com/

SQL Consulting