Thread: Getting a sample data set.
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
On 18 Jan 2011, at 19:02, James B. Byrne wrote:
> 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.
> 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.
Postgres has it's own extension for that, namely DISTINCT ON, used as follows:
SELECT DISTINCT ON (mode) mode FROM shipments ORDER BY mode;
For consistent results in the other column some more ordering would be required, or Postgres would just be returning
thefirst row per mode that it encounters. Then again, that's often what people want in this case anyway.
Standard SQL alternatives tend to get complex, using self-joins to weed out all the records you don't want (the exact
termfor such joins escapes me right now, that would help with Googling if you're looking for examples).
Basically you do something like:
SELECT s1.mode
FROM shipments AS s1
WHERE NOT EXISTS (
SELECT NULL
FROM shipments AS s2
WHERE s1.mode = s2.mode
AND s1.somecolumn < s2.somecolumn
)
Basically you exclude all the records with the same mode that have a larger value for somecolumn than the lowest you
encountered.Only the records with the lowest value for somecolumn remain for each mode. If you turn the sign around for
thatlast condition you'd get the highest value instead.
Important here is that somecolumn doesn't contain any values for the same mode where its values would be considered
equal,or you end up with multiple matches for that mode.
It gets extra interesting if you don't have any columns that are distinct per mode. In such cases you can join your
table(s)against generate_series() or use a windowing function with ranking. This has gotten much easier with our new
CTE's(see the WITH keyword).
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d35dad711701679817192!
On Tue, January 18, 2011 13:23, Alban Hertroys wrote: > > > Standard SQL alternatives tend to get complex, using self-joins to > weed out all the records you don't want (the exact term for such > joins escapes me right now, that would help with Googling if you're > looking for examples). Would the term be a grouped self join? > Basically you do something like: > SELECT s1.mode > FROM shipments AS s1 > WHERE NOT EXISTS ( > SELECT NULL > FROM shipments AS s2 > WHERE s1.mode = s2.mode > AND s1.somecolumn < s2.somecolumn > ) > I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: -- *** 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
On 18 Jan 2011, at 19:59, James B. Byrne wrote: > > On Tue, January 18, 2011 13:23, Alban Hertroys wrote: >> >> >> Standard SQL alternatives tend to get complex, using self-joins to >> weed out all the records you don't want (the exact term for such >> joins escapes me right now, that would help with Googling if you're >> looking for examples). > > Would the term be a grouped self join? Nope, but some Googling put me on the right track. It's called a correlated subquery. > I can see the motivation for something like DISTINCT ON. I take it > that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d35e9d011708045415059!
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> On 18 Jan 2011, at 19:59, James B. Byrne wrote:
>> I can see the motivation for something like DISTINCT ON. I take it
>> that this syntax is peculiar to PostgreSQL?:
> I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :)
Actually, "peculiar to" is perfectly correct here, though a bit
old-fashioned. According to my dictionary, it originally meant
"belonging exclusively to". The meaning of "odd" developed
in the 17th century, long after the other meaning.
regards, tom lane
On Tue, January 18, 2011 14:28, Alban Hertroys wrote: > > Nope, but some Googling put me on the right track. It's called a > correlated subquery. Thank you for this. I will delve further. >> I can see the motivation for something like DISTINCT ON. I take >> it that this syntax is peculiar to PostgreSQL?: > > > I suppose you meant particular? Yes, definitely. Although I'm sure > some would find it peculiar as well :) No. I meant peculiar. As in characteristic of only one person, group, or thing; distinctive . . . to PostgreSQL Regards, -- *** 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
On 18/01/2011 19:34, Tom Lane wrote: > Alban Hertroys<dalroi@solfertje.student.utwente.nl> writes: >> On 18 Jan 2011, at 19:59, James B. Byrne wrote: >>> I can see the motivation for something like DISTINCT ON. I take >>> it that this syntax is peculiar to PostgreSQL?: > >> I suppose you meant particular? Yes, definitely. Although I'm sure >> some would find it peculiar as well :) > > Actually, "peculiar to" is perfectly correct here, though a bit > old-fashioned. According to my dictionary, it originally meant That's a phrase we use in this part of the world also.... or maybe it's just what I learnt from my mother. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 18 Jan 2011, at 23:03, Raymond O'Donnell wrote: >>> I suppose you meant particular? Yes, definitely. Although I'm sure >>> some would find it peculiar as well :) >> >> Actually, "peculiar to" is perfectly correct here, though a bit >> old-fashioned. According to my dictionary, it originally meant > > That's a phrase we use in this part of the world also.... or maybe it's > just what I learnt from my mother. :-) I can't really comment on that, I'm Dutch. English is but my second language. This usage of peculiar is just peculiar tome. But, I've learned something new from you guys, so thanks for that. I'll probably be pestering some British friendswith it in the coming days :) Talk about getting off-topic... I didn't get the impression anyone did mind though. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d362a8a11702229214598!
On 19/01/2011 00:04, Alban Hertroys wrote: > On 18 Jan 2011, at 23:03, Raymond O'Donnell wrote: > >>>> I suppose you meant particular? Yes, definitely. Although I'm >>>> sure some would find it peculiar as well :) >>> >>> Actually, "peculiar to" is perfectly correct here, though a bit >>> old-fashioned. According to my dictionary, it originally meant >> >> That's a phrase we use in this part of the world also.... or maybe >> it's just what I learnt from my mother. :-) > > > I can't really comment on that, I'm Dutch. English is but my second > language. This usage of peculiar is just peculiar to me. But, I've > learned something new from you guys, so thanks for that. I'll > probably be pestering some British friends with it in the coming days > :) The few time I've visited the Netherlands, I got the impression that many Dutch people spoke better English than many native speakers. :-) > Talk about getting off-topic... I didn't get the impression anyone > did mind though. Yeah, getting just a bit OT. :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie