Technical Article

Retrieve every n values

,

this scripts presents the way to retrieve from a given result set the values given n where n determines that the Nth value,2N th values , 3Nth value will be returned

in the example n = 3

the returning rows are 22,32,42

the given set is 20,21,22,30,31,32,40,41,42

SET NOCOUNT ON
CREATE TABLE #valueset (c1 int)
INSERT #valueset VALUES (20)
INSERT #valueset VALUES (30)
INSERT #valueset VALUES (40)
INSERT #valueset VALUES (21)
INSERT #valueset VALUES (31)
INSERT #valueset VALUES (41)
INSERT #valueset VALUES (22)
INSERT #valueset VALUES (32)
INSERT #valueset VALUES (42)

SELECT v.c1
FROM #valueset v CROSS JOIN #valueset a
GROUP BY v.c1
HAVING COUNT(CASE WHEN a.c1 <= v.c1 THEN 1 ELSE null END)%(COUNT(*)/3)=0
GO
DROP TABLE #valueset

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating