How to select distinct records

[Samuray]

Hi everyone...well i was wondering how i can show distinct records from database (basically i want to show distinct records if values in certain column are the same
I tried to use COUNT(*) function and HAVING COUNT(*)>1 to filter some records, i actually got some what less records as the first time...but some data in column are still the same...how can i get rid of showing that data too?)
Thnx for the info...

 

[r937]

that depends

we might be able to help you if we could see your query and also the results that you're getting



r937.com | rudy.ca

 

[sweth]

Hi,
I try to simulate your requirement.
Assuming you have table like that
create table distinctTest
(
id int identity ,
ColA varchar(30),
ColB Varchar(40)
)
declare @i int
set @i = 0
While @i < 100
begin
insert into distinctTest (ColA,ColB) values ('A'+ cast(@i as varchar(30)),'B' + cast(@i as varchar(30)))
set @i=@i+1
end


Here you are inserting dublicate records
-- INSERTING DUPS

While @i < 100
begin
insert into distinctTest (ColA,ColB) values ('A'+ cast(@i as varchar(30)),'B' + cast(@i as varchar(30)))
set @i=@i+1
end

select * from distinctTest

select
COUNT(*)
from
distinctTest A,
distinctTest B
WHERE

a.ColA = b.ColA
and
a.ColB = b.ColB
AND
A.ID > B.ID

The above query returns number of duplicated rows.
Total count - duplicated count gives you distinct count
Hope this help you.

Thanks and Regards
Siva

 

[craigbtg]

This is very basic, but does this work for you?

SELECT distinct(field1), field2 FROM table

 

[r937]

please note, DISTINCT is not a function

r937.com | rudy.ca