By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

highest match in group

P: n/a
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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
"Dave [Hawk-Systems]" <da**@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #4

P: n/a
>"Dave [Hawk-Systems]" <da**@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.