Обсуждение: HELP w/ SQL -- distinct select with non distinct fields?

Поиск
Список
Период
Сортировка

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

От
rlyudmirsky@linkonline.net (RVL)
Дата:
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
------------------------------------------------------------------------