simple select question

[sabret00the]

i have this query:
SELECT grps.groupid, grps.title, grps.create_date, grps.leaderid AS userid, grps_post.userid AS lastposter, grps_post.dateline AS lastpostdateline, COUNT( grps_post.userid ) AS replies
FROM grps_post
LEFT JOIN grps ON ( grps_post.groupid = grps.groupid )
GROUP BY grps_post.groupid
ORDER BY grps_post.dateline ASC


kinda, what i'm trying to make it do is SELECT the first row for the rest of the records, but the last row (i.e. the row with the highest dateline value) from the grps_post table along with the unique line from grps table, what am i doing wrong as i only seem to be able to retreive the first line?

i should know this, i'm pretty sure i do during the day time, but have no clue right now.

[Marco van Herwaarden]

SELECT grps.groupid, grps.title, grps.create_date, grps.leaderid AS userid, grps_post.userid AS lastposter, grps_post.dateline AS lastpostdateline, COUNT( grps_post.userid ) AS replies
FROM grps
LEFT JOIN grps_pst ON ( grps_post.groupid = grps.groupid )
GROUP BY grps.groupid
ORDER BY grps_post.dateline DESC LIMIT 1If i understand you correct, you could try this.

[sabret00the]

that's still bringing up the first row from the grps_post table.

came up with this

SELECT grps.groupid, grps.title, grps.create_date, grps.leaderid AS userid, grps_post.userid AS lastposter, MAX( grps_post.dateline ) AS lastpostdateline, MAX( grps_post.pagetext ) , COUNT( grps_post.userid ) AS replies, MAX( grps_post.postid ) AS postid
FROM grps_post
LEFT JOIN grps ON ( grps_post.groupid = grps.groupid )
GROUP BY grps_post.groupid##, grps_post.postid

ORDER BY grps_post.dateline DESC

is that bad sql?