From: rlyudmirsky@linkonline.net
To: pgsql-sql@postgresql.org
Subject: HELP w/ SQL -- distinct select with non distinct fields?
Date: 23 Sep 2002 15:23:14 -0700
Message-ID: <c5c42943.0209231423.3d143db6@posting.google.com> (raw)
I'm work with Sybase on the Sun... and, being a clueles newbee in SQL
department, hope you could help. I have a set of data:
acct name qty link date memo
101 item_A 100 0001 9/2/02 blah
101 item_A 250 0001 9/3/02 n/a
101 item_A 80 0002 9/3/02 n/a
101 item_B 90 0002 8/8/02 n/a
101 item_B 120 0003 9/7/02 n/a
101 item_B 100 0003 9/2/02 abcd
102 item_B 100 0004 9/3/02 xyz
102 item_B 100 0004 9/7/02 xyz
102 item_C 15 0005 9/1/02 n/a
102 item_C 180 0005 9/5/02 n/a
I need it to be consolidated by [link] and sorted by [acct] [name] and
subtotaled by [qty]. This is easy if I don't use date and memo:
SELECT DISTINCT acct, name, sum(qty), link FROM item_list
GROUP BY acct, name, link ORDER BY acct, name, line
acct name qty link
101 item_A 350 0001
101 item_A 170 0002
101 item_B 220 0003
102 item_B 200 0004
102 item_C 195 0005
However, I want [date] and [memo] from the _first_ record of the group
to be included.
acct name qty link date memo
101 item_A 350 0001 9/2/02 blah
101 item_A 170 0002 9/3/02 n/a
101 item_B 220 0003 8/8/02 n/a
102 item_B 200 0004 9/3/02 xyz
102 item_C 195 0005 9/1/02 n/a
Fields [date] and [memo] are not diplicates, so I cannot consolidate
the set if I add them to SELECT. Is there another way to solve this?
Please help.
Thank you.
P.S. Please forward your reply to my email: rlyudmirsky@linkonline
--
------------------------------------------------------------------------
Rostislav "Steve" Lyudmirsky
rlyudmirsky@linkonline.net
http://rvlstuff.bizland.com
------------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rlyudmirsky@linkonline.net
Subject: Re: HELP w/ SQL -- distinct select with non distinct fields?
In-Reply-To: <c5c42943.0209231423.3d143db6@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox