asked on

How to select a spread of rows in SQL

I'm trying to select a spread of rows based on a value. So if the value is 4, and I have 20 records,I want to select from this data something like the following:

ID      Model

7      3 Series
8      300
10      300M            *
13      350Z
15      4Runner
16      5 Series
18      6 Series
21      7 Series      *
26      9-2X
27      9-3
28      9-5
32      911 Carrera 2
33      911 Carrera 4      *
41      A4
42      A6
43      A8
44      Accent
46      Accord            *
48      Aerio
50      Alero

So the Query would return
10
21
33
46

And would work with any number, any size of table.

I got this far before I asked for help:

select a.Quartile, max(a.RowNumber) as RowNumber from
(
SELECT Model, NTILE(4) OVER(ORDER BY ModelID DESC) AS Quartile, ROW_NUMBER() OVER(ORDER BY ModelID DESC) as RowNumber FROM Models
) a
group by a.Quartile

zephyr_hex (Megan)

You're better off with a CTE rather than a subquery.

But could you explain why, in your example, the record with ID = 10 is returned if the "spread" is 4 ?

zephyr_hex (Megan)

I'm thinking of something along these lines...

/* Data Setup */
/*
SELECT 7 AS Id, CAST('3 Series' AS VARCHAR(20)) AS Model INTO #tmp_test

INSERT INTO #tmp_test SELECT 8, '300' UNION ALL SELECT 10, '300M'
UNION ALL SELECT 13,'350Z' UNION ALL SELECT 15, '4Runner'
UNION ALL SELECT 16, '5 Series' UNION ALL SELECT 18, '6Series'
UNION ALL SELECT 21,'7 Series' UNION ALL SELECT 26,'9-2X'
UNION ALL SELECT 27, '9-3' UNION ALL SELECT 28, '9-5'
UNION ALL SELECT 32, '911 Carrera 2' UNION ALL SELECT 33,'911 Carrera 4'
*/

DECLARE @STEP TINYINT = 4

;WITH cte AS (
	SELECT Id, Model, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
	,NTILE(@STEP) OVER(ORDER BY ID ASC) AS Quartile 
	FROM #tmp_test
)
SELECT * FROM cte
WHERE ...

Open in new window

But I need a better understanding of your interpretation of "spread" because it doesn't seem to line up with the traditional sense of NTILE (or quartitle when "spread" is 4)

ASKER CERTIFIED SOLUTION

zephyr_hex (Megan)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Knight905416

ASKER

You are probably right regarding the use of NTILE. But this solution is looking very promising. But you are welcome to use any thing to solve the problem - except cursors - those won't fly. Basically, we are looking for selecting the ID's from a table and selecting them with a uniform gap between them based on the number entered. And this is real world problem :-)

Here are some screenshots based on your solution. Selecting to many at the moment, but will tweek in the morning, , or let you tweek ;-)
Spread01.JPG
Spread02.JPG

PortletPaul

You're better off with a CTE rather than a subquery

Not true. There is no technical advantage in using cte in the original query. There is no recursion or repetition that could be avoided. Which only leaves personal preference.

My preference is to avoid cte's unless there is a technical advantage.

zephyr_hex (Megan)

Did the query work for you?

Knight905416

ASKER

We ended up using a cursor and this massive beast:

			With rounded
			(val)
			as (
			Select round(val, 2) as val from #vals group by val
			),
			gaps (uBound, lBound, gap) as
			(
				Select top (@goalGaps) with ties val, lag(val) over (order by val) as lBound, val - lag(val) over (order by val) as gap from 
				rounded order by gap desc
			) --select * from gaps 
			,
			gapsMid (uBound, lBound, gap, mid) as (
				Select uBound, lBound, gap, (lBound + uBound) / 2 as mid from gaps
			),
			numTies (numRows, ties, minMid, maxMid, minGap) as
			(
				--Select (Select count(*) from gaps) 
				 Select (Select count(*) from gaps), count(*), min(mid), max(mid), gap from gapsMid group by gap having gap = (Select min(gap) from gaps)
			)
			,
			numTieBreaks (num) as 
			(
				Select top 1 numRows - ties from numTies
			)
			,
			goals(result) as
			(
				Select goal * (numTies.maxMid - numTies.minMid) + numTies.minMid from numTieBreaks inner join lcconfig.dbo.cluster_goals on cluster_goals.num = @goalGaps - numTieBreaks.num
				cross join numTies
			)
			--Select * from goals
			,
			tieBreak (uBound, lBound, gap, mid) as
			(
				select top (Select top 1 num from numTieBreaks) uBound,lBound, gap, mid from gapsMid order by gap desc
				union
				Select gaps1.uBound, gaps1.lBound, gaps1.gap, gaps1.mid --,gaps2.mid, gaps1.gap 
				from goals
				cross join numTies
				left join gapsMid as gaps1 on gaps1.gap = numTies.minGap
				left outer join gapsMid as gaps2 on
					abs(gaps2.mid  - goals.result) < abs(gaps1.mid  - goals.result) and gaps2.gap = numTies.minGap
				where gaps2.mid is null
	
	
				/*Select top  (@goalGaps - (Select top 1 (ties) from numTies)) uBound, lBound, gap, mid from gapsMid order by gap asc, 
				abs((Select top 1 result from modNum) - 
				(((mid - (Select top 1 minMid from numTies)) /(Select top 1 minMid - maxMid from numTies)) %
				(Select top 1 result from modNum))) asc*/
			)
			,
			lastCTE (lBound, uBound) as
			(
				select uBound, lead(lBound) over (order by lBound) as gap from tieBreak
				union 
				select (select top 1 uBound from gaps order by uBound desc), max(val) from rounded
				union
				select min(val), (select top 1 lBound from gaps order by lBound) from rounded 
			)

Open in new window

Knight905416

ASKER

Thanks for the help!