pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I produce the following desired result?
5+ messages / 4 participants
[nested] [flat]
* How can I produce the following desired result?
@ 2003-10-15 02:09 aicean <aicean@netspeed-tech.com>
0 siblings, 2 replies; 5+ messages in thread
From: aicean @ 2003-10-15 02:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I produce the following desired result?
@ 2003-10-15 02:27 Rod Taylor <rbt@rbt.ca>
parent: aicean <aicean@netspeed-tech.com>
1 sibling, 1 reply; 5+ messages in thread
From: Rod Taylor @ 2003-10-15 02:27 UTC (permalink / raw)
To: aicean <aicean@netspeed-tech.com>; +Cc: pgsql-sql
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>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I produce the following desired result?
@ 2003-10-15 03:02 aicean <aicean@netspeed-tech.com>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 5+ messages in thread
From: aicean @ 2003-10-15 03:02 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I produce the following desired result?
@ 2003-10-15 14:48 greg@turnstep.com
parent: aicean <aicean@netspeed-tech.com>
0 siblings, 0 replies; 5+ messages in thread
From: greg@turnstep.com @ 2003-10-15 14:48 UTC (permalink / raw)
To: pgsql-sql; +Cc: aicean@netspeed-tech.com
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I produce the following desired result?
@ 2003-10-15 15:36 Jean-Luc Lachance <jllachan@nsd.ca>
parent: aicean <aicean@netspeed-tech.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2003-10-15 15:36 UTC (permalink / raw)
To: aicean <aicean@netspeed-tech.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-10-15 15:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-15 02:09 How can I produce the following desired result? aicean <aicean@netspeed-tech.com>
2003-10-15 02:27 ` Rod Taylor <rbt@rbt.ca>
2003-10-15 03:02 ` aicean <aicean@netspeed-tech.com>
2003-10-15 14:48 ` greg@turnstep.com
2003-10-15 15:36 ` Jean-Luc Lachance <jllachan@nsd.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox