pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHELP w/ SQL -- distinct select with non distinct fields?
2+ messages / 2 participants
[nested] [flat]
* HELP w/ SQL -- distinct select with non distinct fields?
@ 2002-09-23 22:23 RVL <rlyudmirsky@linkonline.net>
2002-09-23 23:46 ` Re: HELP w/ SQL -- distinct select with non distinct fields? John Gilson <jag@acm.org>
0 siblings, 1 reply; 2+ messages in thread
From: rlyudmirsky@linkonline.net @ 2002-09-23 22:23 UTC (permalink / raw)
To: pgsql-sql
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
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: HELP w/ SQL -- distinct select with non distinct fields?
2002-09-23 22:23 HELP w/ SQL -- distinct select with non distinct fields? RVL <rlyudmirsky@linkonline.net>
@ 2002-09-23 23:46 ` John Gilson <jag@acm.org>
0 siblings, 0 replies; 2+ messages in thread
From: John Gilson @ 2002-09-23 23:46 UTC (permalink / raw)
To: pgsql-sql
"RVL" <rlyudmirsky@linkonline.net> wrote in message
news:c5c42943.0209231423.3d143db6@posting.google.com...
> 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?
It helps to supply a CREATE TABLE and an INSERT so that the
problem is better defined and a proposed solution can be easily tested.
DATE is reserved in SQL so I'll change the column name to the less
readable "d". Also, I believe your result is incorrect.
CREATE TABLE item_list
(
acct INT NOT NULL,
name VARCHAR(10) NOT NULL,
qty INT NOT NULL,
link VARCHAR(5) NOT NULL,
d DATETIME NOT NULL,
memo VARCHAR(10) NOT NULL,
PRIMARY KEY (acct, name, link, d)
)
INSERT INTO item_list
VALUES (101, ' item_A', 100, '0001', '20020902', 'blah')
INSERT INTO item_list
VALUES (101, 'item_A', 250, '0001', '20020903', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_A', 80 , '0002', '20020903', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 90, '0002', '20020808', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 120, '0003', '20020907', 'n/a')
INSERT INTO item_list
VALUES (101, 'item_B', 100, '0003', '20020902', 'abcd')
INSERT INTO item_list
VALUES (102, 'item_B', 100, '0004', '20020903', 'xyz')
INSERT INTO item_list
VALUES (102, 'item_B', 100, '0004', '20020907', 'xyz')
INSERT INTO item_list
VALUES (102, 'item_C', 15, '0005', '20020901', 'n/a')
INSERT INTO item_list
VALUES (102, 'item_C', 180, '0005', '20020905', 'n/a')
SELECT acct,
name,
SUM(qty) AS total,
link,
MIN(d) AS first_date,
(SELECT memo
FROM item_list
WHERE acct = i.acct AND name = i.name AND link = i.link AND d = MIN(i.d)) AS
first_memo
FROM item_list AS i
GROUP BY acct, name, link
ORDER BY acct, name, link
which returns
acct name total link first_date first_memo
101 item_A 350 0001 2002-09-02 00:00:00.000 blah
101 item_A 80 0002 2002-09-03 00:00:00.000 n/a
101 item_B 90 0002 2002-08-08 00:00:00.000 n/a
101 item_B 220 0003 2002-09-02 00:00:00.000 abcd
102 item_B 200 0004 2002-09-03 00:00:00.000 xyz
102 item_C 195 0005 2002-09-01 00:00:00.000 n/a
Regards,
jag
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-23 23:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-23 22:23 HELP w/ SQL -- distinct select with non distinct fields? RVL <rlyudmirsky@linkonline.net>
2002-09-23 23:46 ` John Gilson <jag@acm.org>
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