Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2886F4760A7 for ; Mon, 23 Sep 2002 18:23:22 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id BB572476073 for ; Mon, 23 Sep 2002 18:23:21 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 90DB638167D; Mon, 23 Sep 2002 18:23:20 -0400 (EDT) From: rlyudmirsky@linkonline.net (RVL) X-Newsgroups: comp.databases,comp.soft-sys.sas,comp.databases.postgresql.sql Subject: HELP w/ SQL -- distinct select with non distinct fields? Date: 23 Sep 2002 15:23:14 -0700 Organization: http://groups.google.com/ Lines: 51 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/359 X-Sequence-Number: 9636 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 ------------------------------------------------------------------------