query for distinct records

Question

Hello All,
I have a table named store_master in which store_name, manager,supervisor are the fields.
User have entered values as follows
store_name manager supervisor mumbai a z mumbai b z mumbai c y thane d x thane e x navi mumbai f w
Now I want distinct store_name followed by there last manager and supervisor.
Please help.
Regards,
Zuber Kazi

Santhosh Kumar Jayaraman · Accepted Answer

Thats because i ve used CTE(common table expressions)..
try this
;with cte_Test as
(select *,ROW_NUMBER() over(PARTITION by storename order by storename) as rn from store)
select * from cte_Test a  where rn =(select MAX(rn) from cte_Test b where b.Storename=a.Storename)

Santhosh Kumar Jayaraman · Answer

you have to use ; before with keyword

Zuber Kazi · Answer

Thank You very much Santosh
But if I use it in stored procedure it throws same error.
please help

Zuber Kazi · Answer

Thanx for your help Santosh,
but it throws an error-
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Santhosh Kumar Jayaraman · Answer

Check this.
create table store
(Storename varchar(10),
manager char(10),
supervisor char(2))
insert into store values ('mumbai','a','x')
insert into store values ('mumbai','b','z')
insert into store values ('mumbai','c','y')
insert into store values ('thanae','d','x')
insert into store values ('thanae','e','x')
with cte_Test as
(select *,ROW_NUMBER() over(PARTITION by storename order by storename) as rn from store)
select * from cte_Test a  where rn =(select MAX(rn) from cte_Test b where b.Storename=a.Storename)

store_name	manager	supervisor
mumbai	a	z
mumbai	b	z
mumbai	c	y
thane	d	x
thane	e	x
navi mumbai	f	w