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.

Improve this question
2
  • I suspect this select is select a cell but not a row Commented Sep 2, 2014 at 6:39
  • Your requirement is unreasonable. If you distinct first_name, you should get many ids from one first_name instead of one id. Commented Sep 2, 2014 at 10:47

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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
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.
That's the real issue... if i m going use uniq, it will give me uniq on base of ids not by uniq emails.
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
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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.