Thread: Grouping help

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

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Grouping help


    Hi

    Can someone help me to group my table.

    Current query is :

    Code:
    SELECT 
      public.slcnote.account,
      public.slcnote.keyfield,
      public.slcnote.note_date,
      public.slcnote.note_note,
      public.slcnote.note_type,
      public.slcnote.filter
    FROM
      public.slcnote
    WHERE
      public.slcnote.filter = 'OB' AND 
      public.slcnote.note_date >= '2010-01-01 00:00:00' AND 
      (public.slcnote.note_type = '1' OR 
      public.slcnote.note_type = '2' OR 
      public.slcnote.note_type = '3' OR 
      public.slcnote.note_type = '4' OR 
      public.slcnote.note_type = '5')
    ORDER BY
      public.slcnote.account,
      public.slcnote.keyfield
    This returns data like :

    account1 1234 01/03/2013 note 1 OB
    account1 1235 15/03/2013 note 3 OB
    account1 1244 20/03/2013 note 5 OB
    account2 1222 01/01/2012 note 2 OB
    account2 1299 19/03/2013 note 4 OB

    I want to group and see :

    account1 1244 20/03/2013 note 5 OB
    account2 1299 19/03/2013 note 4 OB

    So basically we total each Account record by the latest keyfield and this displays the last date, note, note type.

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

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    is the combination of account and keyfield unique? If so, make a subselect to get the maximum keyfield for each account, and then select the other corresponding data in the main query:

    Code:
    SELECT
    	...
    FROM
    	slcnote
    WHERE
    	(account, keyfield) IN (
    		SELECT
    			account
    			, MAX(keyfield)
    		FROM
    			slcnote
    		GROUP BY
    			account	
    	)
    ;
    If (account, keyfield) is not unique, then you need additional criteria.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

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

    Keyfield and account are unique


    Hi Thanks

    The above are unique.

    Forgive my ignorance I have never used subselects before.

    Tried this and it is incorrect - how should it be formatted?

    Code:
    SELECT 
      public.slcnote.account,
      public.slcnote.keyfield,
      public.slcnote.note_date,
      public.slcnote.note_note,
      public.slcnote.note_type,
      public.slcnote.filter
    FROM
      public.slcnote
    WHERE
      public.slcnote.filter = 'OB' AND 
      public.slcnote.note_date >= '2010-01-01 00:00:00' AND 
      (public.slcnote.note_type = '1' OR 
      public.slcnote.note_type = '2' OR 
      public.slcnote.note_type = '3' OR 
      public.slcnote.note_type = '4' OR 
      public.slcnote.note_type = '5')
    SELECT
    	
    FROM
    	slcnote
    WHERE
    	(account, keyfield) IN (
    		SELECT
    			account
    			, MAX(keyfield)
    		FROM
    			slcnote
    		GROUP BY
    			account	
    	)
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    This cannot work, because you've simply written two separate queries as if it was one query.

    The SELECT part from your old query goes into the SELECT part of the new query. And your WHERE part goes into the WHERE part of the subselect:

    Code:
    SELECT
    	account
    	, keyfield
    	, note_date
    	, note_note
    	, note_type
    	, filter
    FROM
    	slcnote
    WHERE
    	(account, keyfield) IN (
    		SELECT
    			account
    			, MAX(keyfield)
    		FROM
    			slcnote
    		WHERE
    			filter = 'OB'
    			AND note_date >= '2010-01-01 00:00:00'
    			AND note_type IN ('1', '2', '3', '4', '5')
    		GROUP BY
    			account	
    	)
    ;
    Several things:
    • Don't prepend the "public" schema to every database object (I already said that last time). It's the default schema, so it's unnecessary to explicitly write it down. It just bloats your query.
    • No need to qualify every column with the table name. You only select from one table, so there's never any ambiguity.
    • If you wanna check a column against multiple values, use the IN syntax rather than a long chain of equality checks.
    • Why do you use number strings for the type? Why not actual numbers?
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant 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".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Brilliant thank you.


    This is a great help.

    I will take on board these comments and look to write my queries differently in the future!

    In regards to the note_type field - this is how it was setup by the application manufacturers. Most customers will use this field as text to record information, we use numbers

IMN logo majestic logo threadwatch logo seochat tools logo