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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/bb596858/attachment.html
Larry Kelly 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 BensonHave 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 Figiel -- Posted via http://www.ruby-forum.com/.
why woud you need "distinct" if you use "group by"? On 3/18/06, Agnieszka Figiel <agnieszka.figiel@gmail.com> wrote:> > Larry Kelly 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 Figiel > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/70fd9d19/attachment.html
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 Administrator | | System Administrator | | 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 Administrator | | Web Designer | +----------------------------+ 11 rows in set (0.00 sec) Peace, -Conrad On 3/18/06, Emin Hasanov <emin@hasanov.com> wrote:> > why woud you need "distinct" if you use "group by"? > > > On 3/18/06, Agnieszka Figiel <agnieszka.figiel@gmail.com > wrote: > > > > Larry Kelly 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 Figiel > > > > -- > > Posted via http://www.ruby-forum.com/. > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/9c7a168c/attachment-0001.html
Conrad, the point was that if you use "group by" you don''t need to use "distinct" or the other way around On 3/18/06, Conrad Taylor <conradwt@gmail.com> wrote:> > 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 Administrator | > | System Administrator | > | 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 Administrator | > | Web Designer | > +----------------------------+ > 11 rows in set (0.00 sec) > > > Peace, > > > -Conrad > On 3/18/06, Emin Hasanov <emin@hasanov.com> wrote: > > > > why woud you need "distinct" if you use "group by"? > > > > > > On 3/18/06, Agnieszka Figiel <agnieszka.figiel@gmail.com > wrote: > > > > > > Larry Kelly 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 Figiel > > > > > > -- > > > Posted via http://www.ruby-forum.com/. > > > _______________________________________________ > > > Rails mailing list > > > Rails@lists.rubyonrails.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/6593126d/attachment-0001.html
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 Conrad Taylor wrote:> 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 Administrator | > | System Administrator | > | 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 Administrator | > | Web Designer | > +----------------------------+ > 11 rows in set (0.00 sec) > > > Peace, > > -Conrad > > On 3/18/06, *Emin Hasanov* <emin@hasanov.com > <mailto:emin@hasanov.com>> wrote: > > why woud you need "distinct" if you use "group by"? > > > On 3/18/06, *Agnieszka Figiel* > <agnieszka.figiel@gmail.com > <mailto:agnieszka.figiel@gmail.com>> > wrote: > > Larry Kelly 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 Figiel > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > <mailto:Rails@lists.rubyonrails.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > <mailto:Rails@lists.rubyonrails.org> > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > ------------------------------------------------------------------------ > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
Emin Hasanov wrote:> Conrad, the point was that if you use "group by" you don''t need to use > "distinct" or the other way aroundActually, you asked only one way:> On 3/18/06, *Emin Hasanov* > > 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
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 On 3/19/06, Ray Baxter <ray@warmroom.com> wrote:> > Emin Hasanov 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 Hasanov* > > > > 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 > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/4a2bcf16/attachment.html
Emin Hasanov 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
No problems at all, Ray On 3/19/06, Ray Baxter <ray@warmroom.com> wrote:> > Emin Hasanov 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 > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060318/c95a4048/attachment.html