Replication...

Writing the query on paper requires the candidate to demonstrate the conceptual knowledge of the SELECT-FROM-JOIN-WHERE-ORDER BY construct without fumbling through it by trial-and-error at the keyboard (smart people with...

I would ask an interviewee who presented that solution why he or she did not use the modulo operator, though. It's a bit like asking a candidate for a carpentry...

CELKO (11/29/2012)

I just sit there in my black three piece suit, tenting...

Because it's not yet exist to be used in OVER (ORDER BY ...)

Change it to:

.. ROW_NUMBER() OVER( ORDER BY P.Fsex) ROWID,

...

Try this:

SELECT S.CityId, C.Name

FROM @City AS S

CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name)

WHERE C.Name LIKE @SearchString + '%'

May be.

You have done a great job by specifying table DDL and query you have!

Adding some sample data (as insert script) and expected...

You can use CASE WHEN:

SELECT ...

,CASE WHEN car = 'sedan' THEN 1

WHEN car = 'coupe' THEN 0

...

You don't need to OUTER APPLY, just use relative xPath:

SELECT DISTINCT

S.N.value('name[1]', 'varchar(150)') AS UserName,

S.N.value('id[1]', 'varchar(150)') AS UserID,

S.N.value('../message[1]', 'varchar(150)') AS [Message],

S.N.value('../from[1]', 'varchar(150)') AS [From],

S.N.value('../created_time[1]', 'varchar(150)') AS [CreatedTime],

S.N.value('../likes[1]', 'varchar(150)') AS [Likes]

FROM...

I guess the main problem is in xQuery, you shouldn't have "id " in it:

nodes('/comments_data/Row/from') AS S(N)

Check this:

declare @CommentsData xml =

'<comments_data>

<Row>

<id>204506806280354_433001873430845_1108251</id>

...

Exactly that!

You can generate XML in T-SQL, and it is absolutely fine.

But applying XSLT? It is not good idea.

SQL SERVER is not a right tool for this, actually it doesn't...

You could also use NULLIF (which is wrapper around CASE WHEN...)

SELECT

#Textsample.Event, CONVERT(DATE,NULLIF(#Textsample.Date,''),3)

from #TextSample

Hey, where are you? Mr. One Standard For All or You Can Port My Code To Alpha-Centaurus! :hehe:

I thought you will make another try...

Ok, I might help you a bit:

IBM...

Sorry for my a bit unorthodox view on " FizzBuzz" problem in T-SQL, but couldn't keep myself quiet :hehe:

I don't think it's a good T-SQL question at all..

Create a...