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!

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 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!

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