X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 59BBDD1B504
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 15:34:56 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 75791-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 12:34:25 -0300 (ADT)
Received: from beamish.nsd.ca (beamish.nsd.ca [205.150.156.194])
	by svr1.postgresql.org (Postfix) with ESMTP id C619DD1B524
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 12:34:23 -0300 (ADT)
Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id LAA03032;
	Wed, 15 Oct 2003 11:34:19 -0400
X-Authentication-Warning: beamish.nsd.ca: smap set sender to <jllachan@nsd.ca>
	using -f
Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap
	(V2.1/2.1+anti-relay+anti-spam)
	id xma003030; Wed, 15 Oct 03 11:34:12 -0400
Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148])
	by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id LAA11693;
	Wed, 15 Oct 2003 11:19:37 -0400
Message-ID: <3F8D696D.FA5BFA0D@nsd.ca>
Date: Wed, 15 Oct 2003 11:36:13 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
X-Mailer: Mozilla 4.8 [en] (X11; U; Linux 2.4.18-24.7.x i686)
X-Accept-Language: en
MIME-Version: 1.0
To: aicean <aicean@netspeed-tech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How can I produce the following desired result?
References: <003401c392c1$86c75660$9e00a8c0@aicean>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/175
X-Sequence-Number: 15575

select distinct on( goodid) * from table order by goodid, storehistoryid
desc, totalnum, operationdate;


> aicean wrote:
> 
> How can I produce the following desired result?
> 
>     goodid        totalnum   operationdate storehistoryid
>        132          35.000      09-28-2003             66
>        135          11.500      09-28-2003             61
>        132          35.000      09-27-2003             60
>        135          11.000      09-28-2003             59
>        135          12.000      09-28-2003             58
>        134         100.000      09-28-2003             57
>        134         112.000      09-27-2003             56
>        131           0.000      09-26-2003             54
>        131          33.000      09-26-2003             51
>        131          -2.000      09-26-2003             50
>          3         550.000      09-26-2003             49
>          3          52.000      09-26-2003             48
>        132          35.000      09-27-2003             42
>          3         124.000      09-25-2003             41
>        131          59.000      09-25-2003             40
>        132          57.000      09-26-2003             39
>        131           2.000      09-24-2003             38
>           3           2.000      09-20-2003             23
> 
> result:
>     goodid        totalnum   operationdate storehistoryid
>        132          35.000      09-28-2003             66
>        135          11.500      09-28-2003             61
>        134         100.000      09-28-2003             57
>        131           0.000      09-26-2003             54
>          3         550.000      09-26-2003             49
> 
> I need   to select rows which storehistoryid is max as the same
> goodid .
> 
> 
> Thanks  in advance
> 
> 
>  aicean
>  Mailto:aicean@netspeed-tech.com