sql server - Select distinct statement not working, Active reports and SQL -

i have relatively unique problem, , although have looked 2 days cant find correct answer yet.

in current project using active reports 6 create reports out of data stored on our sql server.

i attempting use select distinct statement no luck.

i using sql create view, not saving view, code run query everytime report requested.

the report show current vehicles company has on lease. report must bring unique hits on registration field. can in there multiple times, if more 1 person has driven vehicle etc..

here sql code

select distinct on registration companycars  companycars.registration regno, [staff details].[first name] + ' ' + [staff details].[last name] driver, companycars.car_type vehicletype,     companycars.reg_date regdate, companycars.price price,   [staff details].[ni number] ninumber,  companycars.end_mile - companycars.start_mile usedmiles, companycars.lease_mile -(companycars.end_mile - companycars.start_mile) excessmiles, case when companycars.lease_mile -(companycars.end_mile - companycars.start_mile) < 0 , companycars.excess <> 0 (companycars.end_mile - companycars.start_mile)- companycars.lease_mile * (companycars.excess / 100) else 0 end overunder, companycars.excess excessrate, companycars.company company    [staff details] inner join [companycarhistory] on [staff details].staffref = [companycarhistory].staffref  inner join companycars on companycars.registration = [companycarhistory].registration 

any ideas anyone? sql isn't great.

paul

i put yours code subselect (not tested):

select * (     select      row_number() on (partition companycars.registration order companycars.reg_date desc) rowid     companycars.registration regno,     [staff details].[first name] + ' ' + [staff details].[last name] driver,     companycars.car_type vehicletype,         companycars.reg_date regdate,     companycars.price price,       [staff details].[ni number] ninumber,      companycars.end_mile - companycars.start_mile usedmiles,     companycars.lease_mile -(companycars.end_mile - companycars.start_mile) excessmiles,     case when companycars.lease_mile -(companycars.end_mile - companycars.start_mile) < 0 , companycars.excess <> 0 (companycars.end_mile - companycars.start_mile)- companycars.lease_mile * (companycars.excess / 100) else 0 end overunder,     companycars.excess excessrate,     companycars.company company         [staff details] inner join [companycarhistory] on [staff details].staffref = [companycarhistory].staffref      inner join companycars on companycars.registration = [companycarhistory].registration ) temp rowid = 1