Grouping help for a noob!

Dev Shed Forums Sponsor:

November 28th, 2012, 10:45 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 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

November 28th, 2012, 11:08 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,870

Time spent in forums: 1 Month 2 Weeks 2 Days 23 m 4 sec
Reputation Power: 813

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).

November 29th, 2012, 03:16 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

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

December 10th, 2012, 06:28 PM

joeflinthiggins

Registered User

Join Date: May 2012

Posts: 14

Time spent in forums: 4 h 20 m 33 sec
Reputation 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.

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Grouping help for a noob!

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump