By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

difference when using 'distinct on'

P: n/a
Howdy:

Can someone tell what the difference (and why
you would use it) is between the following:

[snip]
select distinct on (col_1, col_2),
col_1,
col_2,
col_3
from t_table

--

select distinct
col_1,
col_2,
col_3
from t_table
[/snip]

In the first example, is it just getting
the unique rows for the first two columns?

Thanks!

-X

Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Fri, Sep 12, 2003 at 09:29:39 -0400,
"Johnson, Shaunn" <SJ*******@bcbsm.com> wrote:

In the first example, is it just getting
the unique rows for the first two columns?


Yes. Normally you would use distinct on in combination with an order by
so that you get the particular unique row you are interested in.
For example if col1 and col2 were an event type and col3 was a timestamp
for the event, you could use distinct on to get the earliest or latest
timestamp for each event type.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #2

P: n/a
On Fri, Sep 12, 2003 at 09:29:39 -0400,
"Johnson, Shaunn" <SJ*******@bcbsm.com> wrote:

In the first example, is it just getting
the unique rows for the first two columns?


Yes. Normally you would use distinct on in combination with an order by
so that you get the particular unique row you are interested in.
For example if col1 and col2 were an event type and col3 was a timestamp
for the event, you could use distinct on to get the earliest or latest
timestamp for each event type.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #3

P: n/a
On Fri, 12 Sep 2003, Johnson, Shaunn wrote:
Howdy:

Can someone tell what the difference (and why
you would use it) is between the following:

[snip]
select distinct on (col_1, col_2),
col_1,
col_2,
col_3
from t_table

--

select distinct
col_1,
col_2,
col_3
from t_table
[/snip]

In the first example, is it just getting
the unique rows for the first two columns?


In the first, for each set of rows that have a distinct col1,col2
value it's taking one of those rows and using its col3 value.
It's like group by, but less restrictive since you don't need
to use a set function on col_3.

In general distinct on in that fashion is most usable when
combined with an order by so that you can get a particular row
from each set. For example, you might say do something like:
select distinct on (col1, col2) col1, col2, col3 from t_table
order by col1, col2, col4;
In this case you should get the col3 value for each col1,col2
distinct group that corresponds to the row having the lowest col4
value.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #4

P: n/a
On Fri, 12 Sep 2003, Johnson, Shaunn wrote:
Howdy:

Can someone tell what the difference (and why
you would use it) is between the following:

[snip]
select distinct on (col_1, col_2),
col_1,
col_2,
col_3
from t_table

--

select distinct
col_1,
col_2,
col_3
from t_table
[/snip]

In the first example, is it just getting
the unique rows for the first two columns?


In the first, for each set of rows that have a distinct col1,col2
value it's taking one of those rows and using its col3 value.
It's like group by, but less restrictive since you don't need
to use a set function on col_3.

In general distinct on in that fashion is most usable when
combined with an order by so that you can get a particular row
from each set. For example, you might say do something like:
select distinct on (col1, col2) col1, col2, col3 from t_table
order by col1, col2, col4;
In this case you should get the col3 value for each col1,col2
distinct group that corresponds to the row having the lowest col4
value.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.