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

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)

you have to use ; before with keyword

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

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.

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)

Answers

query for distinct records

Zuber Kazi

12y

2.1k

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

Answers (5)

You'd need to replace "\\" with @"\" :

ConfigurationSettings.AppSettings["Location"] + @"\" + Format_Date + @"\" + custName + "(" + pId + ")" + @"\";

Not really worth doing.

Accepted

You are saying to have the value as: C:\ntemp\ntestt1\ncustomer? This will cause my value to

be C:\temp\testt1\customer in the database?

How would a verbatim work for the following: ConfigurationSettings.AppSettings["Location"] + "\\" + Format_Date + "\\" + custName + "(" + pId + ")" + "\\";

No. I'm saying to pass this C# string:

"C:\\temp\\testt1\\customer"

to the database i.e. with doubled backslashes.

You could also use a 'verbatim' string (preceded by @) with single backslashes:

@"C:\temp\testt1\customer"

Both of these represent the same string in C#.

Just save the string inclusive of the doubled backslashes which are needed in C# to distinguish the use of the backslash in escape sequences (such as \n, \t etc).

It should then save in the database as a path with single backslashes

Next Recommended Forum

How to combine a multiple rows in Sql 2005?

Is it is posible to fetch the data using cursor through sql 2005 in Asp.net?

Upcoming events

View all

Forum Statistics

Please welcome our newest member .
users have contributed to threads and
In the past 24 hours, we have new threads, new posts, and new users.
In last week, the most popular thread is .

query for distinct records

Vulpes

dc

dc

Vulpes

Vulpes