Query Issue

Databases
1

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

Mark

2

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 
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

:slight_smile:

3

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