473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

difference when using 'distinct on'

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
4 7944
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: MasterChief | last post by:
I have a Drop Down box that is being populated using the SQL Distinct command. How do I get it so it doesn't show Null values? Here is my SQL statement sSQL = "SELECT DISTINCT Title FROM...
0
by: Johnson, Shaunn | last post by:
Howdy: Can someone tell what the difference (and why you would use it) is between the following: select distinct on (col_1, col_2), col_1, col_2, col_3
8
by: skinnybloke | last post by:
Hi - I have a problem with a memo field being truncated to about 255 characters when running a Access 2002 query. This only seems to happen if I use SELECT DISTINCT. It works ok using SELECT by...
24
by: wwj | last post by:
Hi ,all I want to know the difference between char a and char *p=new char and the difference between the heap and the stack ,and if the char a is corresponding to the stack in MEMORY,and char...
10
by: tinesan | last post by:
Hello fellow C programmers, I'm just learning to program with C, and I'm wondering what the difference between signed and unsigned char is. To me there seems to be no difference, and the...
2
by: pankajit09 | last post by:
Hi What is the difference between unique and distinct in an SQL ?
1
by: chella | last post by:
Hi, Is there any difference in using select distinct and select unique? select distinct column_name from table_name select unique column_name from table_name If both gives the same...
2
by: baburk | last post by:
hi, In these two Query, for join we have to use DISTINCT but for SubQuery no need to use distinct. SELECT DISTINCT PM.PropertyID, PM.PropertyName FROM PropertyMaster PM JOIN...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.