Here's another way

WITH CTE AS (
 SELECT CityID, MonthID, LowTemp, HiTemp,ROW_NUMBER() OVER(PARTITION BY CityID ORDER BY MonthID) AS rn
 FROM dbo.CityTemperature
 WHERE LowTemp >= 64
)
SELECT m.MonthName, c.CityName
FROM...

Maybe this?

select [User]
from mytable
group by [User]
having count(ProcessId) > count(distinct ProcessId);

This looks like a 'relational division' problem. Solution here not pretty but should work.

WITH Grps AS (
SELECT PartNumber,COUNT(*) AS GroupCount
FROM #parts
GROUP BY PartNumber
),
OrderedSrc AS (
SELECT PartNumber,PartValue,ROW_NUMBER() OVER(PARTITION...

• This reply was modified 2 years, 4 months ago by  Mark Cowne.
• This reply was modified 2 years, 4 months ago by  Mark Cowne.

Yes you could, that would work...

That will match PageDefintions and...

Try this

select ID,detail_form,
      (len(detail_form) - len(replace(replace(replace(detail_form,'<PageDefintion>','<>'),'<PageDefintion/>','</>'),'</PageDefintion>','</>'))) / len('PageDefintion') as PageDefintionCount
from mytable

Maybe this?

WITH CTE AS (
SELECT t.id,
       j2.[key] AS field,
   j2.value AS val,
   row_number() over(partition by j2.[key] order...

DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

• This reply was modified 2 years, 6 months ago by  Mark Cowne.
• This reply was modified 2 years, 6 months ago by  Mark Cowne.

Small tweak to Phils code should do it

SELECT
    q1.success
,   q1.timestamp
,   q1.date
,   q1.base
,   q1.unit
,   q2.[key]...

• This reply was modified 2 years, 7 months ago by  Mark Cowne.

SELECT id,
       MIN(Game_Time) AS Game_Start,
       MAX(Game_Time) AS Game_END
FROM Game
GROUP BY id
ORDER BY id;

• This reply was modified 2 years, 7 months ago by  Mark Cowne.

SELECT Date,ID,CHAR(ASCII('A') + (ROW_NUMBER() OVER(PARTITION BY Date ORDER BY ID) -1) % 3) AS Grp
FROM #s
ORDER BY Date,ID;

@shogunSQL, this should work for you.

SELECT C.x.value('@Id', 'varchar(20)') as Id 
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('title[1]', 'varchar(100)') as Title
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)

Another way

SELECT C.x.value('@Id', 'varchar(20)') as Id 
,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
FROM @doc.nodes('ROOT/*/*/item') as C(x)

Deleted, wrong solution

• This reply was modified 2 years, 10 months ago by  Mark Cowne.
• This reply was modified 2 years, 10 months ago by  Mark Cowne.
• This reply was modified 2 years, 10 months ago by  Mark Cowne.
• This reply was modified 2 years, 10 months ago by  Mark Cowne. Reason: Problems with square brackets
• This reply was modified 2 years, 10 months ago by  Mark Cowne.

Not totally clear to me what you're asking for, maybe this?

with cte as (
select REF_NO,
       n.x.value('Country[1]','varchar(30)') as Country,
   ...