Thread
-
How can I produce the following desired result?
aicean <aicean@netspeed-tech.com> — 2003-10-15T02:09:11Z
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 -
Re: How can I produce the following desired result?
Rod Taylor <rbt@rbt.ca> — 2003-10-15T02:27:47Z
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> -
Re: How can I produce the following desired result?
aicean <aicean@netspeed-tech.com> — 2003-10-15T03:02:12Z
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?
-
Re: How can I produce the following desired result?
Greg Sabino Mullane <greg@turnstep.com> — 2003-10-15T14:48:37Z
-----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-----
-
Re: How can I produce the following desired result?
Jean-Luc Lachance <jllachan@nsd.ca> — 2003-10-15T15:36:13Z
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