#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Grouping help for a noob!


    Hi

    I am trying to group my data so that I only see one row per "account" field and then show the max of the date, the last note and the last note_type records

    My query so far is not working - can someone please help? Thank you.

    Code:
    SELECT 
      public.slcnote.account,
      MAX(public.slcnote.note_date) AS field_1,
      public.slcnote.note_note,
      public.slcnote.note_type,
      public.slcnote.filter
    FROM
      public.slcnote
    WHERE
      public.slcnote.company = 1 AND 
      public.slcnote.filter = 'TSAL'
    GROUP BY
      public.slcnote.account,
      public.slcnote.note_note,
      public.slcnote.note_type,
      public.slcnote.filter      
    ORDER BY
    public.slcnote.account
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,926
    Rep Power
    1045
    Hi,

    what does "not work" mean? Do you get an unexpected result? An error message? Does your computer blow up?

    By the way, you don't need to prepend the "public" schema to every identifier (unless you've fumbled with the schema search path).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Apologies


    Hi

    Apologies for the lazy comment "not working" - and no the PC hasn't blown up!

    Data is being returned like:

    acct | date | note | type | filter
    2th 01/01/2012 a123 TC TSAL
    2th 25/11/2012 b321 TC TSAL

    I want to see

    2th 25/11/2012 b321 TC TSAL

    Only

    Hope this helps you understand, thanks
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    Since you are grouping by 4 different columns, you will see the MAX(public.slcnote.note_date) for each combination of those 4 columns. In the results you pasted, the value for the note column is different for each row (a123 and b321).

    If you don't want the note column to be considered when identifying the MAX(), then don't select or group by that column.

IMN logo majestic logo threadwatch logo seochat tools logo