3

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"

Monica
  • 41
  • 7
  • What have you tried so far? – ilkerkaran Nov 12 '18 at 22:09
  • 1
    1) Tried looking into an equivalent for "DISTINCT ON" postgresql terminology" 2) Tried aggregating data and picking the first c.id from each cgi - I don't think the aggregate modifier is the way to go 3) Tried to make the equivalent of the best answer's second query here: https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – Monica Nov 12 '18 at 22:52

1 Answers1

1

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

Monica
  • 41
  • 7
  • if you end up writing raw SQL too often, you may want to check out https://github.com/layerware/hugsql – nha Nov 14 '18 at 19:10