Re: HELP w/ SQL -- distinct select with non distinct fields? - Mailing list 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.linkAND 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