Query Issue

Hi guys, new here so go easy :)

I have 2 tables in a Joomla database

User table
Items table

The items table contains short stories written by users. The stories are held in the items table

I need a query that contains one line for each user with the total number of stories they have written

Code:

---

select u.username, i.title, i.created, count(i.id) as totalstories from jos_k2_items i inner join jos_users u on u.id = i.created_by group by u.username asc order by u.username, i.id desc

---

Results returned like this
Username Title, created, totalstories
User A My story 23/05/2010 25
User B Another story 16/09/2010 17

The aggreagate part works fine

The problem I have is that each user will have a number of stories eg 25 but we are only showing one line per user as a summary

I need it to show the last item (story) created by the user and the last create date and the query currently returns the first even if I put the story id as desc sort order.

I'm sure it's something obvious so any help gratefully appreciated!

Many thanks

Mark

it's only obvious if you understand that "hidden" fields are indeterminate (see GROUP BY and HAVING with hidden columns)

try this --

Code:

---

SELECT u.username
    , i.title
    , i.created
    , m.totalstories
  FROM jos_users AS u
INNER
  JOIN ( SELECT created_by
              , COUNT(*) AS totalstories
              , MAX(created) AS latest
          FROM jos_k2_items
        GROUP
            BY created_by ) AS m
    ON m.created_by = u.id
INNER
  JOIN jos_k2_items AS i
    ON i.created_by = u.id
  AND i.created = m.latest
ORDER
    BY u.username

---

:)

That works perfectly, thank you so much for your prompt reply, much appreciated :)