Re: How to findout Eliminated record by when use Distinct function

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 18:33:02 +0530

>>i want to find out which record it will eliminate from the set. I found that it will
eliminate based on
alphabetical and record sequence in the table.<<

No there is no order in which the rows will be retrieved from the table. When you run SELECT
statement against a table there is no assurance the order of the resultset. SQL server returns
the result set whichever way is quickest for it. If you have a primary key along with it then
you can re-write your query so that alongwith each COL1 value which pk value will be retrieved
ex: using min/max function alongwith group by.

See following example.

create table #cartype(manufacturer varchar(500), score int)
insert into #cartype values('Toyota', 1 )
insert into #cartype values('Toyota', 2)
insert into #cartype values('Toyota', 3)
insert into #cartype values('Honda', 4)
insert into #cartype values('Honda', 5)
insert into #cartype values('Honda', 6)
insert into #cartype values('GE', 7)
insert into #cartype values('GE', 8)
insert into #cartype values('GE', 9)
insert into #cartype values('GE', 10)

--query to get distinct of manufactuer and max value of primary key.
select manufacturer, max(score) score
from #cartype
group by manufacturer

--query to get eliminated records from above query will be

select * from #cartype a
where not exists
(select manufacturer, max(score) score
from #cartype b
where a.manufacturer = b.manufacturer
group by manufacturer
having a.score = max(b.score)) 

-- 
Vishal Parkar
vgparkar@yahoo.co.in