Search code examples
sqlruby-on-railspostgresqlgreatest-n-per-grouppg

how to make DISTINCT, ORDER BY, CASE work together?

Required sort by condition - if field is exclusive, then sort by "fees"."exclusive_price" else by "fees"."additional_price".
SQL completely looks like this:

SELECT DISTINCT "numbers".* 
FROM "numbers" 
INNER JOIN "users_numbers" ON "users_numbers"."number_id" = "numbers"."id" 
INNER JOIN "users" ON "users"."id" = "users_numbers"."user_id" 
INNER JOIN "fees" ON "fees"."user_id" = "users"."id" 
WHERE "numbers"."state" != 'removed' 
ORDER BY CASE "numbers"."is_exclusive" WHEN TRUE THEN "fees"."exclusive_price" ELSE "fees"."additional_price" END desc"

But I get an error (in rails):

ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block

Error because of distinct. Without distinct - success.
How to correct SQL?

Added columns from related tables to SELECT - didn't help:

SELECT DISTINCT "fees"."exclusive_price", "fees"."additional_price", "sender_numbers".*
FROM ...
Solution

  • A plain DISTINCT de-duplicates based on the complete SELECT list. The (final) ORDER BY step then only accepts expressions that are part of that SELECT list.

    To order by that CASE expression, you'd have to include it in the SELECT list. Like:

    SELECT DISTINCT n.*, CASE WHEN n.is_exclusive THEN f.exclusive_price ELSE f.additional_price END AS order_col
FROM   numbers n
JOIN   users_numbers un ON un.number_id = n.id 
JOIN   users         u  ON u.id = un.user_id 
JOIN   fees          f  ON f.user_id = u.id 
WHERE  n.state <> 'removed' 
ORDER  BY order_col DESC;

    You would have to wrap that in an outer query to remove order_col from the SELECT list. But I am not sure, we have the complete picture, yet. And there may be simpler ways ...