Rails - Jan 2006 - Using SQL to get a whole record when using aggregate functions

I have a table with some data structured something like this:

book
----
title (varchar)
pages (int)
completed_on (date)

While I can find the shortest book completed in each year with  
something like this:

select min( pages ), year from book group by year

I would also like to get the title of the shortest book in each year.  
Is there any way to do this by modifying the previous query to pull  
that as well in a single pass? The only approach I can think of is to  
iterate through the results of the above query, doing another query  
for each shortest book I want to get all the details for, which seems  
a mite inefficient.

Thanks, SQL mavens!

Sean

-----
Sean McMains
AIM: SeanMcTex
http://www.mcmains.net/ruminations


-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060109/475c760e/attachment.html

On Mon, Jan 09, 2006 at 01:38:26PM -0600, Sean McMains wrote:
} I have a table with some data structured something like this:
} 
} book
} ----
} title (varchar)
} pages (int)
} completed_on (date)
} 
} While I can find the shortest book completed in each year with  
} something like this:
} 
} select min( pages ), year from book group by year
} 
} I would also like to get the title of the shortest book in each year.  
} Is there any way to do this by modifying the previous query to pull  
} that as well in a single pass? The only approach I can think of is to  
} iterate through the results of the above query, doing another query  
} for each shortest book I want to get all the details for, which seems  
} a mite inefficient.

You should realize that if any given year has more than one book of the
minimal number of pages for that year you will get all of them. I will also
assume that you have a year field, which you do not mention. That said:

SELECT b.*
FROM book b JOIN (
	SELECT MIN(pages), year
	FROM book
	GROUP BY year
	) m ON m.year = b.year AND m.pages = b.pages

This requires an RDBMS that supports nested queries. PostgreSQL, as well as
essentially all major commercial RDBMSs do. I have heard that MySQL still
does not, though that may have changed by now. This also requires an RDBMS
that understands the JOIN keyword, though it can be rephrased to use a
WHERE clause instead.

} Thanks, SQL mavens!
} Sean
--Greg

Sean:

I forget the exact method but you do something like this (get the  
exact details from www.mysql.com/dev  where they have their manual on  
line).

You type select title, min(pages) as pages, year from book group by  
year then use then either

use a ''having'' clause (look that one up)\

or

order by min(pages) limit 1

Something along that line.  Sorry I can''t help more, just at a  
client''s right now.

bruce


On 9-Jan-06, at 12:38 PM, Sean McMains wrote:
> I have a table with some data structured something like this:
>
> book
> ----
> title (varchar)
> pages (int)
> completed_on (date)
>
> While I can find the shortest book completed in each year with  
> something like this:
>
> select min( pages ), year from book group by year
>
> I would also like to get the title of the shortest book in each  
> year. Is there any way to do this by modifying the previous query  
> to pull that as well in a single pass? The only approach I can  
> think of is to iterate through the results of the above query,  
> doing another query for each shortest book I want to get all the  
> details for, which seems a mite inefficient.
>
> Thanks, SQL mavens!
>
> Sean
>
> -----
> Sean McMains
> AIM: SeanMcTex
> http://www.mcmains.net/ruminations
>
>
> _______________________________________________
> 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/20060109/4a8b6bd1/attachment.html