January 4, 2016 at 4:32 am
Hello All
Attached is a resultset I am getting.
In this result set i have to return the count of the rows inbetween the name and the next time the
Name comes up.
Please if someone can point me in the right direction.
January 4, 2016 at 5:37 am
Try this. It relies on there being a unique key in your table, which I've represented by the column OrderbyThisCol.
CREATE TABLE Names (
OrderbyThisCol int NOT NULL PRIMARY KEY
,Name varchar(10) NOT NULL
);
INSERT INTO Names VALUES
(1, 'Mary')
,(2, 'John')
,(3, 'Pieter')
,(4, 'Mary')
,(5, 'Pieter')
,(6, 'Mary')
,(7, 'John')
,(8, 'Harry')
,(9, 'Pieter')
,(10, 'Pieter');
WITH NamesOrdered AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY OrderbyThisCol) RowNo
,ROW_NUMBER() OVER (ORDER BY OrderbyThisCol) Ordering
,OrderbyThisCol
,Name
FROM Names
)
SELECT
OrderByThisCol
,Name
,CASE RowNo
WHEN 1 THEN 0
ELSE Ordering - LAG(Ordering,1,0) OVER (PARTITION BY Name ORDER BY Ordering) - 1
END AS CountBetween
FROM NamesOrdered
ORDER BY OrderbyThisCol;
John
January 4, 2016 at 6:12 am
Another quick solution, similar to John's apart from this one is counting in both directions
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* SAMPLE DATA SET */
;WITH SAMPLE_DATA(RN,NAME) AS
(
SELECT 1,'Mary' UNION ALL
SELECT 2,'John' UNION ALL
SELECT 3,'Pieter' UNION ALL
SELECT 4,'Mary' UNION ALL
SELECT 5,'Pieter' UNION ALL
SELECT 6,'Mary' UNION ALL
SELECT 7,'John' UNION ALL
SELECT 8,'Harry' UNION ALL
SELECT 9,'Pieter' UNION ALL
SELECT 10,'Pieter'
)
,BASE_DATA AS
(
SELECT
SD.RN
,SD.NAME
,LAG(SD.RN,1) OVER
(
PARTITION BY SD.NAME
ORDER BY SD.NAME
,SD.RN
) AS LAG_RN
,LEAD(SD.RN,1) OVER
(
PARTITION BY SD.NAME
ORDER BY SD.NAME
,SD.RN
) AS LEAD_RN
FROM SAMPLE_DATA SD
)
SELECT
BD.RN
,BD.NAME
,(BD.RN - BD.LAG_RN) - 1 AS PREV_ROW_DIFF
,(BD.LEAD_RN - BD.RN) - 1 AS NEXT_ROW_DIFF
FROM BASE_DATA BD
ORDER BY BD.RN ASC;
Results
RN NAME PREV_ROW_DIFF NEXT_ROW_DIFF
----------- ------ ------------- -------------
1 Mary NULL 2
2 John NULL 4
3 Pieter NULL 1
4 Mary 2 1
5 Pieter 1 3
6 Mary 1 NULL
7 John 4 NULL
8 Harry NULL NULL
9 Pieter 3 0
10 Pieter 0 NULL
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply