Query Issue

Hi guys, new here so go easy :slight_smile:

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

 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


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

try this –

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


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