How do I write this SQL the Rails way?

I’m trying to find all the unique bill_number, status records in Bills
table. I can do it with a find_by_sql statement like this:

@records = Bill.find_by_sql( “select distinct bill_number, status
from bills
group by bill_number,
status;”)

How would I rewrite it using ‘find :all …’ ??


Best Regards,
-Larry
“Work, work, work…there is no satisfactory alternative.”
— E.Taft Benson

Larry K. wrote:

I’m trying to find all the unique bill_number, status records in Bills
table. I can do it with a find_by_sql statement like this:

@records = Bill.find_by_sql( “select distinct bill_number, status
from bills
group by bill_number,
status;”)

How would I rewrite it using ‘find :all …’ ??


Best Regards,
-Larry
“Work, work, work…there is no satisfactory alternative.”
— E.Taft Benson

Have a look at “find” in the ActiveRecord::Base API. This should
probably be:
Bill.find(:all,
:select => “distinct bill_number, status”,
:group => “bill_number”)


Agnieszka F.

why woud you need “distinct” if you use “group by”?

Hi, DISTINCT keyword is used when you only want to select the unique
values
of the given column. For example,

select title from employee_data;

±---------------------------+
| title |
±---------------------------+
| CEO |
| Senior Programmer |
| Senior Programmer |
| Web Designer |
| Web Designer |
| Programmer |
| Programmer |
| Programmer |
| Programmer |
| Multimedia Programmer |
| Multimedia Programmer |
| Multimedia Programmer |
| Senior Web Designer |
| System A. |
| System A. |
| Senior Marketing Executive |
| Marketing Executive |
| Marketing Executive |
| Marketing Executive |
| Customer Service Manager |
| Finance Manager |
±---------------------------+
21 rows in set (0.00 sec)

You’ll notice that the display contains multiple occurences of certain
data.
The SQL DISTINCT clause lists only unique data. Here is how you use
it.

select DISTINCT title from employee_data;

±---------------------------+
| title |
±---------------------------+
| CEO |
| Customer Service Manager |
| Finance Manager |
| Marketing Executive |
| Multimedia Programmer |
| Programmer |
| Senior Marketing Executive |
| Senior Programmer |
| Senior Web Designer |
| System A. |
| Web Designer |
±---------------------------+
11 rows in set (0.00 sec)

Peace,

-Conrad

The better question would have been, why use “group by” when you use
“distinct”? With distinct, there is only one result of each type, so
each group has exactly one member. There is nothing to group.

Ray

Emin H. wrote:

Conrad, the point was that if you use “group by” you don’t need to use
“distinct” or the other way around

Actually, you asked only one way:

On 3/18/06, *Emin H.*

    why woud you need "distinct" if you use "group by"?

Group by is superfluous if you use distinct. Distinct is not superfluous
if you use group by.

Adding distinct to a group by modifies the results. If your table of
roles contains:

Programmer
Programmer
Manager
Programmer

select role from table group by role =>
Programmer
Programmer
Programmer
Manager

select distinct role from table =>
Programmer
Manager

select distinct role from table group by role =>
Programmer
Manager

Ray

Conrad, the point was that if you use “group by” you don’t need to use
“distinct” or the other way around

Emin H. wrote:

Ray,

Did you actually run your queries?

Obviously not.

“select role from table group by role” and “select distinct role from
table” are identical queries and produce list of unique records.

Emin,

I am totally wrong. I apologize for the noise and for correcting you.

Ray

Ray,

Did you actually run your queries?

“select role from table group by role” and “select distinct role from
table”
are identical queries and produce list of unique records.

Emin

No problems at all, Ray

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs