highest match in group
От | Dave [Hawk-Systems] |
---|---|
Тема | highest match in group |
Дата | |
Msg-id | DBEIKNMKGOBGNDHAAKGNKEJBHNAC.dave@hawk-systems.com обсуждение исходный текст |
Ответы |
Re: highest match in group
Re: highest match in group |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: