Grouping help

Dev Shed Forums Sponsor:

March 19th, 2013, 04:57 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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!

March 19th, 2013, 05:19 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,847

Time spent in forums: 1 Month 2 Weeks 1 Day 10 h 36 m 10 sec
Reputation Power: 813

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 • The mystery of errors and exceptions

March 19th, 2013, 05:26 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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	
	)

March 19th, 2013, 05:42 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,847

Time spent in forums: 1 Month 2 Weeks 1 Day 10 h 36 m 10 sec
Reputation Power: 813

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?

March 19th, 2013, 06:04 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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

Developer Shed Advertisers and Affiliates