November 19, 2008 at 2:19 am
I understand what this statement does:
select distinct(names) from tableA
But what about this one?
select distinct names, address, cities from tableA.
Does the "distinct" apply to the entire record (names, address, cities) returned in the record set or only to the "Names" field? I have been unable to find any clear, comprehensive, documentation on this "obvious" keyword.
TIA,
Barkingdog
November 19, 2008 at 2:32 am
Hello,
In your second example, Distinct applies to the whole record.
To quote BOL: “The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement”
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 19, 2008 at 2:35 am
John Marsh (11/19/2008)
Hello,In your second example, Distinct applies to the whole record.
Regards,
John Marsh
yes distinct applies to the whole record and sql created the duplicates where it requires to fill the cell for getting the distinct result with the previous value
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 19, 2008 at 2:35 am
This is one of the cases that you can do a very quick test to find out how it works instead of taking the word of me or anyone else:-). Run the code bellow and see exactly how distinct works.
create table demo (col1 int, col2 int)
go
insert into demo (col1, col2)
select 1, 1
union all
select 1,2
union all
select 1,1
union all
select 2,3
go
select col1, col2
from demo
go
select distinct col1
from demo
go
select distinct col2
from demo
go
select distinct col1, col2
from demo
go
drop table demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 19, 2008 at 12:13 pm
Great answer. Thanks to everyone.
barkingdog
November 20, 2008 at 6:37 am
I would caution about the use of DISTINCT, though
we have an application here which tries to find a DISTINCT key using a multiple table JOIN from one table to put on another
if there are multiple records the whole thing falls apart - yet another record gets added(!), the key field stays NULL
if the logic used a CASE statement with a COUNT instead the earliest key could have been used or something like that
November 21, 2008 at 1:01 am
I'll keep my eyes "open" when using DISTINCT.
BD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply