Forum: Ruby on Rails Using SQL to get a whole record when using aggregate functio

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.
096a09594a8de0ef479f0365864536fd?d=identicon&s=25 Sean McMains (Guest)
on 2006-01-09 20:41
(Received via mailing list)
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
675475d0b65710be6d992eb5eb2c61c2?d=identicon&s=25 Gregory Seidman (Guest)
on 2006-01-09 21:02
(Received via mailing list)
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
This topic is locked and can not be replied to.