Forum: Ruby on Rails How do I write this SQL the Rails way?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Larry K. (Guest)
on 2006-03-18 12:57
(Received via mailing list)
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
Agnieszka F. (Guest)
on 2006-03-18 14:30
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.
Emin H. (Guest)
on 2006-03-18 15:44
(Received via mailing list)
why woud you need "distinct" if you use "group by"?
Conrad T. (Guest)
on 2006-03-18 21:50
(Received via mailing list)
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
Emin H. (Guest)
on 2006-03-18 22:51
(Received via mailing list)
Conrad, the point was that if you use "group by" you don't need to use
"distinct" or the other way around
Ray B. (Guest)
on 2006-03-18 23:03
(Received via mailing list)
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
Ray B. (Guest)
on 2006-03-18 23:22
(Received via mailing list)
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
Emin H. (Guest)
on 2006-03-18 23:43
(Received via mailing list)
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
Ray B. (Guest)
on 2006-03-19 02:35
(Received via mailing list)
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
Emin H. (Guest)
on 2006-03-19 02:35
(Received via mailing list)
No problems at all, Ray
This topic is locked and can not be replied to.