PARTITION BY Kullanımı

Mesajların kategorileriyle beraber tutulduğu bir tablomuz olsun.

postgres=# select * from messages;  
id  | key     | message                | created_at  
----+---------+------------------------+---------------------------- 
1   | contact | send from contact page | 2015-10-31 19:45:16.850698  
2   | contact | contact page message   | 2015-10-31 19:45:34.417231  
3   | product | product 1              | 2015-10-31 19:45:44.49584  
4   | product | product 2              | 2015-10-31 19:45:46.856691  
5   | contact | hello it is me         | 2015-10-31 18:45:35.801967  
6   | about   | who are you            | 2015-10-31 19:46:04.123369  
7   | product | product 3              | 2015-10-31 19:46:12.414364  
8   | about   | hi guys                | 2015-10-31 19:46:18.23442  
(8 rows)

Örneğin; contact, product, ve about kategorisine sahip mesajlardan sadece en son geleni göstermek istiyoruz.

 key     | message              | created_at 
---------+----------------------+---------------------------- 
about    | hi guys              | 2015-10-31 19:46:18.23442  
contact  | contact page message | 2015-10-31 19:45:34.417231  
product  | product 3            | 2015-10-31 19:46:12.414364 (3 rows)  

Böyle bir sonucu aşağıdaki sorguyu kullanarak elde edebiliriz.

SELECT key, message, created_at FROM ( SELECT key, message, created_at, rank() OVER (PARTITION BY key ORDER BY created_at DESC) AS rank FROM messages ) AS foo WHERE foo.rank = 1;  

Erkin Çakar

PostgreSQL DBA & Software developer