Search code examples
clojurekormasqlkorma

Imitate partitions/window functions in sqlkorma

I am trying to call the equivalent of this function using sqlkorma, and have not found the equivalent of a partition in the documentation (http://sqlkorma.com/docs): 

SELECT * FROM (
SELECT DISTINCT cgi, scgi, c.id, c.name, c.address, c.city,
c.state_or_province, c.postal_code, primary_country,
    ROW_NUMBER() OVER (
    PARTITION BY cgi
) AS ROW_NUMBER
from ccs
join c on c.id = ccs.id) groups
WHERE groups.ROW_NUMBER = 1 ORDER BY cgi

For clarity, in my database, there is a one-to-many relationship columns variables "cgi" and "c.id"

Solution

  • Found a simple solution to this - sqlkorma allows you to execute raw SQL, and I specified the postgresql DISTINCT ON (column_to_partition_on), column_to_partition_on, column1, column2 FROM table. Using that as the query text, this is the general format to execute raw sql using korma:

    (exec-raw my-database query-text :results)

    More info at http://sqlkorma.com/docs