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

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Add field to query


    Hi

    I need to add a field to a query that someone kindly helped me with yesterday:

    I have added a field called operator (integer) but when I run the query I now get multiple rows for accounts where I need to show the last row.

    For example I want to show the highlighted riw:

    acct1 operator1 01/01/2011
    acct1 operator2 01/03/2013

    Here is the query that I amended - unsuccesfully!

    Code:
    SELECT account, operator, keyfield, note_date, note_note, note_type, filter
       FROM slcnote
      WHERE ((account::text, keyfield) IN ( SELECT account, max(keyfield) AS max
               FROM slcnote
              WHERE filter::text = 'OB'::text AND note_date >= '2010-01-01 00:00:00+00'::timestamp with time zone AND (note_type::text = ANY (ARRAY['1'::character varying, '2'::character varying, '3'::character varying, '4'::character varying, '5'::character varying]::text[]))
              GROUP BY account, operator))
              ORDER BY account ASC;
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    the GROUP BY clause is wrong. When you group by both account and operator, each combination of those two values form a separate group. So you get the latest row for each (account, operator) rather than for each account.

    Remove the "operator" from the GROUP BY clause, and you're done.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Thanks


    Thanks yet again for great help!

IMN logo majestic logo threadwatch logo seochat tools logo