Just for fun...

select s.Id,s.Name

from courses c

INNER JOIN school s ON c.SchoolId = s.Id

where c.Name = 'maths'

intersect

select s.Id,s.Name

from courses c

INNER JOIN school s ON c.SchoolId = s.Id

where c.Name = 'english'

select id as "info/@count",

xmlcol as "info"

from mytable

for xml path(''),root('infos')

delete u1

FROM cn_users u1

join cn_users u2 on u1.userid = u2.userid

and u1.id > u2.id

Untested, but try this

select s.Id,s.Name

from courses c

INNER JOIN school s ON c.SchoolId = s.Id

where c.Name in ('maths' ,'english')

group by s.Id,s.Name

having count(distinct c.Name) >= 2

Which ID do you want?

select name,max(id) as id

from test

group by name

GSquared (3/27/2009)

---

Does this help?

declare @XML XML;

select @XML = ' ';

select x.y.value('local-name(.)', 'VARCHAR(50)'),

x.y.value('.', 'VARCHAR(50)')

from @XML.nodes('//*') x(y);

That's sample XML, but it pulls name-value pairs from it, and that sounds like what...

May be this?

with cte(productname,turnover) as (

Select case when row_number() over (order by sum(turnover) desc) >= 5 then 'Other products' else productname end,

sum(turnover)

from sales

group...

sys.objects.name is NVARCHAR

SELECT @ssql = @ssql + 'Drop procedure ' + CAST(name AS VARCHAR(128)) + ' '

Try these

Select tablename.query('data(//XMlnode/text())')

Select tablename.query('data(//XMlnode//text())')

This looks more like something that is handled better in your front end

select coalesce(col1,'col3') as col1,col2

from (select col1,col2,col3

from mytable

...

change

select empid, [1],[2] from (select empid, workingHrs, onDate, month(ondate) as d from rota) a

to

select empid, [1],[2] from (select empid, workingHrs, month(ondate) as d from rota) a

I think the problem is the unicode->ascii conversion that is happening somewhere along the way. C# strings are always 16-bit.

Try changing

...

Try this...

CREATE FUNCTION [dbo].[ccsc_admis_numbers3New](

@type varchar(1),

@year int,

...

Use CASE expression

SELECT CASE WHEN SKU LIKE '%EC%' THEN 'EC'

WHEN SKU LIKE '%DD%' THEN 'DD'

...

You need a way to fully define your ordering to give you a "first".

See if either of these help

with cte as (

select entry_id,group_id,cost,

row_number()...