SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query Issue

  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works perfectly, thank you so much for your prompt reply, much appreciated


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •