Re: HELP w/ SQL -- distinct select with non distinct fields?

"RVL" <rlyudmirsky(at)linkonline(dot)net> wrote in message
news:c5c42943(dot)0209231423(dot)3d143db6(at)posting(dot)google(dot)com(dot)(dot)(dot)
> 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