Count of same value in order

Question

Count of same value in order

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
August 11, 2016 at 6:16 am

#328874

CREATE TABLE Test
(ID char(1));
with cte as
(
select 'x' as ID
union all
select 'x' as ID
union all
select 'x' as ID
union all
select 'x' as ID
union all
select 'A' as ID
union all
select 'A' as ID
union all
select 'x' as ID
union all
select 'x' as ID
)
insert into test (id) select ID from cte;
Select of Table Test:
ID
X
X
X
X
A
A
X
X
Output of count:
X -4
A-2
X-2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

There's currently no column to set the order of these rows. Without one, SQL Server will not guarantee that the rows will be processed (output) in any specific order. A suitable column might be IDENTITY or date/time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

DROP TABLE #temp;

WITH cte as (

select 1 AS ID, 'x' as Item union all

select 2, 'x' union all

select 3, 'x' union all

select 4, 'x' union all

select 5, 'A' union all

select 6, 'A' union all

select 7, 'x' union all

select 8, 'x'

)

SELECT * INTO #temp FROM cte;

-- If there are gaps or dupes in ID

-- then you should use ROW_NUMBER() OVER(ORDER BY ID)

WITH GroupedData AS (

SELECT ID, Item,

grp = ID - ROW_NUMBER() OVER(PARTITION BY Item ORDER BY ID)

FROM #temp

)

SELECT Item, COUNT(*)

FROM GroupedData

GROUP BY Item, grp

ORDER BY MIN(ID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden