Re: [GENERAL] Getting a sample data set.
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. Byrnemailto:byrn...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
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 the first 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 term for 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 that last 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
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. Byrnemailto:byrn...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
Alban Hertroys dal...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
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. Byrnemailto:byrn...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
On 18/01/2011 19:34, Tom Lane wrote: Alban Hertroysdal...@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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
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 :) 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
