Thread: How can I produce the following desired result?

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
wantedgoodid>      ) AS tab      USING (goodid)WHERE <other clauses> 

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.



aiceanMailto: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?

-----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-----

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