How do I write this SQL the Rails way?

Rails
1

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

2

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.

3

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

4

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

5

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

6

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

7

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

8

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

9

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

10

No problems at all, Ray