From aicean@netspeed-tech.com Wed Oct  15 02:13:12 2003
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 9A50DD1B531
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 02:13:12 +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 30667-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Oct 2003 23:12:44 -0300 (ADT)
Received: from netspeed-tech.com (unknown [218.104.80.12])
	by svr1.postgresql.org (Postfix) with SMTP id 883DBD1B4FC
	for <pgsql-sql@postgresql.org>; Tue, 14 Oct 2003 23:12:36 -0300 (ADT)
Received: (qmail 24403 invoked from network); 15 Oct 2003 02:05:39 -0000
Received: from unknown (HELO aicean) (192.168.0.158)
	by 0 with SMTP; 15 Oct 2003 02:05:39 -0000
Message-ID: <003401c392c1$86c75660$9e00a8c0@aicean>
From: "aicean" <aicean@netspeed-tech.com>
To: <pgsql-sql@postgresql.org>
Subject: How can I produce the following desired result?
Date: Wed, 15 Oct 2003 10:09:11 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002F_01C39304.60D63870"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=HTML_50_60,
	HTML_FONT_FACE_ODD
X-Spam-Level: 
X-Archive-Number: 200310/167
X-Sequence-Number: 15567

This is a multi-part message in MIME format.

------=_NextPart_000_002F_01C39304.60D63870
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

How can I produce the following desired result?

    goodid        totalnum   operationdate storehistoryid=20
       132          35.000      09-28-2003             66=20
       135          11.500      09-28-2003             61=20
       132          35.000      09-27-2003             60=20
       135          11.000      09-28-2003             59=20
       135          12.000      09-28-2003             58=20
       134         100.000      09-28-2003             57=20
       134         112.000      09-27-2003             56=20
       131           0.000      09-26-2003             54=20
       131          33.000      09-26-2003             51=20
       131          -2.000      09-26-2003             50=20
         3         550.000      09-26-2003             49=20
         3          52.000      09-26-2003             48=20
       132          35.000      09-27-2003             42=20
         3         124.000      09-25-2003             41=20
       131          59.000      09-25-2003             40=20
       132          57.000      09-26-2003             39=20
       131           2.000      09-24-2003             38=20
          3           2.000      09-20-2003             23=20
=20=20=20=20=20=20
result:

    goodid        totalnum   operationdate storehistoryid=20
       132          35.000      09-28-2003             66=20
       135          11.500      09-28-2003             61=20=20=20=20=20=20
       134         100.000      09-28-2003             57=20
       131           0.000      09-26-2003             54=20
         3         550.000      09-26-2003             49=20=20=20=20=20=20=
=20=20=20=20

I need   to select rows which storehistoryid is max as the same goodid .


Thanks  in advance


 aicean
 Mailto:aicean@netspeed-tech.com

------=_NextPart_000_002F_01C39304.60D63870
Content-Type: text/html;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dgb2312">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DVerdana size=3D2>How can I produce the following desired=
=20
result?</FONT></DIV>
<DIV><FONT face=3DVerdana size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia>&nbsp;&nbsp;&nbsp;=20
goodid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; totalnum&nbsp;&nbsp;=20
operationdate storehistoryid <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
35.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
66 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
11.500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
61 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
35.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-27-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
60 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
11.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
59 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
12.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
58 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
134&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
57 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
134&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
112.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-27-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
56 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
54 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
33.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
51 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
-2.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
50 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
550.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
49 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
52.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
48 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
35.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-27-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
42 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
124.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-25-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
41 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
59.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-25-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
40 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
57.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
39 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
131&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
2.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-24-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
38&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
2.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-20-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
23&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT face=3DGeorgia>result:<BR></FONT></DIV>
<DIV><FONT face=3DGeorgia>&nbsp;&nbsp;&nbsp;=20
goodid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; totalnum&nbsp;&nbsp;=20
operationdate storehistoryid <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
35.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
66 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
11.500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
61&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
134&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
100.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-28-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
57&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;131&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
54&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
550.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
09-26-2003&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
49&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</FONT></DIV>
<DIV><FONT face=3DGeorgia></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia>I need&nbsp;&nbsp;&nbsp;to select rows which=20
storehistoryid&nbsp;is&nbsp;max as the same goodid&nbsp;.</FONT></DIV>
<DIV><FONT face=3DGeorgia></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia>Thanks&nbsp; in advance</DIV></FONT>
<DIV><FONT face=3DGeorgia></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia></FONT>&nbsp;</DIV>
<DIV><FONT face=3DGeorgia>&nbsp;aicean<BR>&nbsp;<A=20
href=3D"mailto:aicean@netspeed-tech.com">Mailto:aicean@netspeed-tech.com</A=
><BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_002F_01C39304.60D63870--




From rbt@rbt.ca Wed Oct  15 02:28:20 2003
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 14281D1B50E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 02:28:20 +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 37118-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Oct 2003 23:27:52 -0300 (ADT)
Received: from mail.hive.nj2.inquent.com (mc.carriermail.com [205.178.180.9])
	by svr1.postgresql.org (Postfix) with SMTP id 7F637D1B4FC
	for <pgsql-sql@postgresql.org>; Tue, 14 Oct 2003 23:27:47 -0300 (ADT)
Received: (qmail 22563 invoked from network); 15 Oct 2003 02:27:12 -0000
Received: from unknown (HELO ?192.168.1.199?) (134.22.69.154)
	by 205.178.180.9 with SMTP; 15 Oct 2003 02:27:12 -0000
Subject: Re: How can I produce the following desired result?
From: Rod Taylor <rbt@rbt.ca>
To: aicean <aicean@netspeed-tech.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <003401c392c1$86c75660$9e00a8c0@aicean>
References: <003401c392c1$86c75660$9e00a8c0@aicean>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-vdnGCkGCoBLJS9ru9zMJ"
Message-Id: <1066184866.99719.9.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.5 
Date: Tue, 14 Oct 2003 22:27:47 -0400
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/168
X-Sequence-Number: 15568

--=-vdnGCkGCoBLJS9ru9zMJ
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-10-14 at 22:09, aicean wrote:
> How can I produce the following desired result?

I'm not sure I understand the problem, but you might want to try a
subselect in the FROM.

SELECT <wanted results>
  FROM table
  JOIN (SELECT goodid
          FROM table
         WHERE <whatever you did to get list of wanted goodid>
       ) AS tab
       USING (goodid)
 WHERE <other clauses>

--=-vdnGCkGCoBLJS9ru9zMJ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/jLCi6DETLow6vwwRAsNFAJ0bSZXY/iTzjvXZOTrzE2qHQ3ETWwCeMfBF
knR1QpocbUy89O5CWQ9BhbU=
=uixn
-----END PGP SIGNATURE-----

--=-vdnGCkGCoBLJS9ru9zMJ--




From aicean@netspeed-tech.com Wed Oct  15 03:04:27 2003
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 90B00D1B537
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 03:04:27 +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 50235-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 00:03:56 -0300 (ADT)
Received: from netspeed-tech.com (unknown [218.104.80.12])
	by svr1.postgresql.org (Postfix) with SMTP id C4ADFD1B540
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 00:03:54 -0300 (ADT)
Received: (qmail 25354 invoked from network); 15 Oct 2003 02:57:13 -0000
Received: from unknown (HELO aicean) (192.168.0.158)
	by 0 with SMTP; 15 Oct 2003 02:57:13 -0000
Message-ID: <017c01c392c8$baf24560$9e00a8c0@aicean>
From: "aicean" <aicean@netspeed-tech.com>
To: "Rod Taylor" <rbt@rbt.ca>
Cc: <pgsql-sql@postgresql.org>
References: <003401c392c1$86c75660$9e00a8c0@aicean>
	<1066184866.99719.9.camel@jester>
Subject: Re: How can I produce the following desired result?
Date: Wed, 15 Oct 2003 11:02:12 +0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/169
X-Sequence-Number: 15569

Thanks very much.
   It seems can solve my problem.For select the max "storehistoryid",I have
to compare each row's storehistoryid.
Any other advice?
 Thanks again.




 aicean
 Mailto:aicean@netspeed-tech.com

----- Original Message ----- 
From: "Rod Taylor" <rbt@rbt.ca>
To: "aicean" <aicean@netspeed-tech.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, October 15, 2003 10:27 AM
Subject: Re: [SQL] How can I produce the following desired result?





From greg@turnstep.com Wed Oct  15 14:49:19 2003
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 37087D1B54B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 14:49:19 +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 64777-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Oct 2003 11:48:52 -0300 (ADT)
Received: from biglumber.com (biglumber.com [207.228.252.42])
	by svr1.postgresql.org (Postfix) with SMTP id B483ED1B544
	for <pgsql-sql@postgresql.org>; Wed, 15 Oct 2003 11:48:47 -0300 (ADT)
Received: (qmail 3842 invoked from network); 15 Oct 2003 14:48:37 -0000
Received: from unknown (HELO localhost) (207.228.252.42)
	by 0 with SMTP; 15 Oct 2003 14:48:37 -0000
From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Cc: aicean@netspeed-tech.com
Cc: aicean@netspeed-tech.com
Subject: Re: How can I produce the following desired result?
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
X-Request-PGP: 
 http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
In-Reply-To: <017c01c392c8$baf24560$9e00a8c0@aicean>
Date: Wed, 15 Oct 2003 14:48:37 -0000
X-Mailer: JoyMail 1.38
Message-ID: <e4ac6a52d3f01e6dfe0a2d31c2f5b5b5@biglumber.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/174
X-Sequence-Number: 15574


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> How can I produce the following desired result?

SELECT * FROM mytable
JOIN 
(SELECT goodid, MAX(storehistoryid) AS storehistoryid FROM mytable GROUP by 1) AS a
USING (goodid,storehistoryid);


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200310151046

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/jV4PvJuQZxSWSsgRAmYlAJwL06D+VNkmAT7RDcjXPgGu9oPXkgCgx1SJ
OASzEJlWv6qi05xXhPH1NBY=
=szda
-----END PGP SIGNATURE-----






From jllachan@nsd.ca Wed Oct  15 15:34:56 2003
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