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

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Date Max Group help


    Hi

    I have a simple that I need to add grouping to, but don't really know where to start! Can someone please help?

    The query

    Code:
    SELECT 
      public.slcname.account,
      public.slcname.surname,
      public.slcname.last_contact,
      public.slcname.next_contact
    FROM
      public.slcname
    WHERE
      public.slcname.surname ILIKE '%TEL%' AND 
      public.slcname.surname NOT ILIKE '%PATEL%'      
    ORDER BY public.slcname.account asc
    Example data

    account surname last_contact next_contact
    2th Sue Tel 16/11/2012 15:16 14/12/2012 08:00
    2th Telesales 29/11/2010 11:53 01/02/2013 07:00

    What I need to do is group by account and show the last date the account ic contacted and the next time to be called

    Any help would be great
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

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

    is there a unique key on (account, last_contact)? If not, you need some additional criterion to form a unique identifier, because the maximum date for a certain account isn't enough to specify a certain row. Something like: Always use the highest primary key.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Added ID field


    Hi

    I have added the table keyfield to the query. So how would I now group as desired?

    Code:
    SELECT 
      public.slcname.keyfield,
      public.slcname.account,
      public.slcname.surname,
      public.slcname.last_contact,
      public.slcname.next_contact
    FROM
      public.slcname
    WHERE
      public.slcname.surname ILIKE '%TEL%' AND 
      public.slcname.surname NOT ILIKE '%PATEL%'
    ORDER BY
      public.slcname.account
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    I think you misunderstood my reply. You cannot do a GROUP BY yet, because your specification isn't sufficient.

    Let's say you have this:
    Code:
    account		last_contact		next_contact
    2th		16/11/2012		14/12/2012
    2th		16/11/2012		15/12/2012
    Let 16/11/2012 be MAX(last_contact) for account = 2th. What's the corresponding next_contact? 14/12/2012 or 15/12/2012?

    I'll assume you want the latest next_contact:
    Code:
    SELECT
    	account
    	, last_contact AS latest_last_contact
    	, MAX(next_contact) AS next_contact
    FROM
    	slcname
    WHERE
    	(account, last_contact) IN (
    		SELECT
    			account
    			, MAX(last_contact)
    		FROM
    			slcname AS latest_last_contacts
    		GROUP BY
    			account	
    	)
    GROUP BY
    	account, last_contact
    ORDER BY
    	account ASC
    Last edited by Jacques1; January 30th, 2013 at 06:52 AM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Great Thanks


    Thanks this has done what I needed it to.

IMN logo majestic logo threadwatch logo seochat tools logo