2

I was trying to answer this question where I got this issue.

I have a user model having id, email and first_name columns. So in single query I want to select users with distinct first_name, sort them by email and pluck their ID.

How can I do this?

what won't work:

  1. User.select(:first_name).uniq.pluck(:id)

    because it fires this SQL SELECT DISTINCT "users"."id" FROM "users". This is selecting distinct id from user. But we want to select distinct first_name

  2. User.order("email DESC").pluck(:id).uniq

    SQL generated: SELECT "users"."id" FROM "users" ORDER BY email DESC. This will not work because the sql generated is not checking for uniqueness of first_name.

Community
  • 1
  • 1
Hardik
  • 3,815
  • 3
  • 35
  • 45

1 Answers1

3

You could test the SQL with this SQLFIDDLE.

Only Sqlite and MySql support this usage, but others don't.

Refer to this postgresql document.

In the SQL-92 standard, an ORDER BY clause can only use result column names or numbers

It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT result list. Thus the following statement is valid:

SELECT name FROM distributors ORDER BY code;

A limitation of this feature is that an ORDER BY clause applying to the result of a UNION, INTERSECT, or EXCEPT clause can only specify an output column name or number, not an expression.

For your case, there is no need of using uniq, all user id distinct already, so why don't you try this:

User.order("email DESC").pluck(:id)

Assuming the ids duplicated, you could uniq by ruby instead of DB.

User.order("email DESC").pluck(:id).uniq

This script will never generate sql with distinct. This uniq after pluck is an Array#uniq method.

Community
  • 1
  • 1
Jaugar Chang
  • 3,176
  • 2
  • 13
  • 23
  • I have simplified the example with `User` table..... Actually There is this case where I need to pluck the column `a` after ordering them by column `b` and getting only uniq entries by column `c`. In such case I need to use `pluck`, `uniq` and `select` together – Hardik Sep 02 '14 at 09:23
  • My second script in the answer will get ids array ordered by email, and if it's duplicate, with `Array#uniq` method, returns the uniq ids ordered by email. But how can you get column `c` from an collection only have `a` column? I don't get it. – Jaugar Chang Sep 02 '14 at 09:36
  • That's the real issue... if i m going use uniq, it will give me uniq on base of ids not by uniq emails. – Hardik Sep 02 '14 at 10:09
  • `But how can you get column c from an collection only have a column? I don't get it.`... that must be the reason i m getting this error. looks like there is no option left other then writing raw sql – Hardik Sep 02 '14 at 10:11
  • Assuming we have id and email arrays like this: `[[1,'a'],[1,'b'],[2,'c']]`, with my second script you can get ids ordered by email desc and uniq like `[2,1]`. Is that you want? Can you create some sample input an output data on your question? – Jaugar Chang Sep 02 '14 at 10:15
  • `But how can you get column c from an collection only have a column?` If you can create the sample data by this scenario, it might be some solution without raw sql. – Jaugar Chang Sep 02 '14 at 10:22