Обсуждение: highest match in group
have a data table that records entries by date(unix timestamp) and customer number. each custnum will have several entries showing a running ledger type snapshot. we have the need to get the most recent entry from not one, but all unique customers, in the most cost effective manner. Table "summary" Attribute | Type | Modifier -----------+---------+---------- custnum | integer | date | integer | amount | float8 | balance | float8 | sample data; custnum | date | amount | balance ----------+------------+---------+--------- 12025702 | 1019151676 | 47.96 | 0 12045401 | 1019145600 | 17.12 | -17.12 12040601 | 1019229292 | 26.7 | 1.02 12045701 | 1019232000 | 16.59 | -16.59 12045702 | 1019232000 | 16.59 | -16.59 12045703 | 1019232000 | 9.87 | -9.87 12045704 | 1019232000 | 16.59 | -16.59 12045705 | 1019232000 | 16.59 | -16.59 12045704 | 1019408919 | 15.52 | -1.07 12045704 | 1019404800 | 15.52 | -16.59 Currently we are running through all our customer numbers in one query, then for each customer number querying the summary table to get each customers latest entry (select order by date desc limit 1). Obviously this results in a large number of queries and is expensive. Looking for a more concise, less expensive way. thanks Dave
Dave [Hawk-Systems] wrote: > have a data table that records entries by date(unix timestamp) and customer > number. each custnum will have several entries showing a running ledger type > snapshot. we have the need to get the most recent entry from not one, but all > unique customers, in the most cost effective manner. > > [snip] > > Currently we are running through all our customer numbers in one query, then > for each customer number querying the summary table to get each customers > latest entry (select order by date desc limit 1). Obviously this results in a > large number of queries and is expensive. Looking for a more concise, less > expensive way. > > thanks > > Dave What about something like: SELECT so.* FROM summary so, (SELECT custnum, MAX(date) as date FROM summary si GROUP BY custnum) as cd WHERE so.date = cd.date AND so.custnum = cd.custnum Best regards, Arjen van der Meijden
"Dave [Hawk-Systems]" <dave@hawk-systems.com> writes: > have a data table that records entries by date(unix timestamp) and customer > number. each custnum will have several entries showing a running ledger type > snapshot. we have the need to get the most recent entry from not one, but all > unique customers, in the most cost effective manner. If you don't mind using a Postgres-only construct, SELECT DISTINCT ON is made for this. See the "weather report" example in the SELECT reference page. regards, tom lane
Is it possible to update multiple columns of a table using a select statement to derive the values? For example (trying to port from Oracle to Postgres) -- update the image record for original size image update WPImage set (WPImageStateID, Width, Height, ContentType, ContentLength) = ( select 3, Width, Height, ContentType, ContentLength from WPImageHeader where WDResourceID = pResourceID ) where WDResourceID = pResourceID and WPSizeTypeID = 0; I have seen mention of a Postgres (specific) feature, update ... set .... from {other_table} where {join_condition} Is this the most appropriate way to do the above in postgres? update WPImage set WPImageStateID = 3, Width = WPImageHeader.Width, Height = WPImageHeader.Height, ContentType = WPImageHeader.ContentType, ContentLength = WPImageHeader.ContentLength where WPImage.WDResourceID = WPImageHeader.WDResourceID and WPImage.WDResourceID = pResourceID and WPImage.WPSizeTypeID = 0; {where pResourceID is a variable} Thanks John Sidney-Woollett
>"Dave [Hawk-Systems]" <dave@hawk-systems.com> writes: >> have a data table that records entries by date(unix timestamp) and customer >> number. each custnum will have several entries showing a running >ledger type >> snapshot. we have the need to get the most recent entry from not >one, but all >> unique customers, in the most cost effective manner. > >If you don't mind using a Postgres-only construct, SELECT DISTINCT ON >is made for this. See the "weather report" example in the SELECT >reference page. thanks Tom, had a forest for the trees problem there and that put me on the right track. Dave