I am working with Ruby on Rails and I have stumbled into a situation
which turned out to be, surprisingly for me, somewhat involved.
Given a table "shipments" having a column called "mode" I want to
extract one entire shipment row (all columns) for each distinct
value of mode. Assuming that there are 1700 rows and that there are
just five distinct values in use for mode then I want to return five
rows with all their columns and each one having a different value
for mode.
If I use the distinct clause then I only return the rows making up
the distinct clause. Employing this approach produces either many
more matches than I want or only returns the mode column.
While I could not accomplish this with a single ORM call to
ActiveRecord I solved this using an iterator inside RoR. My
programmatic solution was:
> x = Shipment.select("DISTINCT(mode)")
> ms = []
> x.each do |s|
> ms << Shipment.find_by_mode(s.mode)
> end
Which gives me a collection of rows each having a different mode.
But now I am curious how this is done in plain SQL. I have have not
found any useful guide as to how to approach this problem in the
reference materials I have to hand. I cannot believe that I am the
first person to require this sort of thing of SQL so if anyone can
point me to a reference that explicitly sets out how to accomplish
this I would greatly appreciate it.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3