highest match in group

Поиск
Список
Период
Сортировка
От Dave [Hawk-Systems]
Тема highest match in group
Дата
Msg-id DBEIKNMKGOBGNDHAAKGNKEJBHNAC.dave@hawk-systems.com
обсуждение исходный текст
Ответы Re: highest match in group  (Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>)
Re: highest match in group  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Casting Point to Text
Следующее
От: Arjen van der Meijden
Дата:
Сообщение: Re: highest match in group