Home - General / All posts - Select sum of top n records grouped by
 adamw8,696 post(s) #22-Apr-14 07:44 The straightforward way of doing this failed due to a limitation of the query engine in Manifold 8, so here's a roundabout way.Say, you want to sum top 3 highest values. Then, create 1+3+1 queries, as follows:Q - for each road segment, find tie lines joining into it, to get geometry out of the way:--SQLSELECT R.ID RID, T.ID TID, T.[Adjusted Length] LFROM [Rural] R INNER JOIN [Tie lines 5280'] T ON Touches(R.ID, T.ID)Q1 - for each road segment, find highest value for a tie line joining into it:--SQLSELECT R.ID, (SELECT TOP 1 TID T1, L L1 FROM Q WHERE R.ID=Q.RID ORDER BY L DESC)FROM [Rural] RQ2 - for each road segment, find 2 highest values for tie lines joining into it:--SQLSELECT R.ID, R.T1, R.L1, (SELECT TOP 1 TID T2, L L2 FROM Q WHERE R.ID=Q.RID AND R.T1<>Q.TID ORDER BY L DESC)FROM [Q1] RQ3 - for each road segment, find 3 highest values for tie lines joining into it:--SQLSELECT R.ID, R.T1, R.L1, R.T2, R.L2, (SELECT TOP 1 TID T3, L L3 FROM Q WHERE R.ID=Q.RID AND R.T1<>Q.TID AND R.T2<>Q.TID ORDER BY L DESC)FROM [Q2] RQS - sum highest values:--SQLSELECT R.ID, R.L1+R.L2+R.L3 L FROM [Q3] RHope this helps.I guess you can do everything after Q easily with a script, without losing too much performance. The benefit would be that you'd only need one script component no matter how many highest values you'd like to sum.
 Mike Pelletier1,629 post(s) #22-Apr-14 16:13 Thanks a lot for this Adam. Although I'd prefer no query engine limitation, it's a bit gratifying that after banging away on this for quite awhile, the solution wasn't simple. When googling for an answer on this, I ran across a post that someone uses this question as a test for employment (non-Manifold SQL engines). The poster said only 5% or so can get it even with no time limit. You passed!The script option sounds appealing since I was thinking of n = 20. My scripting skills are rather rusty so if there is a quick bit of code you could post that would get me going it would be much appreciated.
 adamw8,696 post(s) #22-Apr-14 16:52 Here's a slightly modified query Q:--SQLSELECT R.ID RID, T.ID TID, T.[Adjusted Length] LFROM [Rural] R INNER JOIN [Tie lines 5280'] T ON Touches(R.ID, T.ID)ORDER BY RID, L DESCAnd a script:'VBScriptFunction CreateResultTable  Set columnSet = Application.NewColumnSet  Set column = columnSet.NewColumn  column.Name = "RID"  column.Type = ColumnTypeInt32  columnSet.Add column  Set column = columnSet.NewColumn  column.Name = "SumL"  column.Type = ColumnTypeInt32  columnSet.Add column  Set CreateResultTable = Document.NewTable("Result", columnSet)End Function Sub AddResultRecord(recSet, rid, sumL)  Set rec = recSet.NewRecord  rec.Data("RID") = rid  rec.Data("SumL") = sumL  recSet.Add recEnd Sub Sub Main  Set tableOut = CreateResultTable  Set recsOut = tableOut.RecordSet  Set tableIn = Document.ComponentSet("Q").Table  Set recsIn = tableIn.RecordSet  lastCount = 0  lastRID = 0  lastL = 0  For Each recIn In recsIn    thisRID = CLng(recIn.Data("RID"))    thisL = CLng(recIn.Data("L"))    If lastCount > 0 And lastRID <> thisRID Then      AddResultRecord recsOut, lastRID, lastL      lastCount = 0      lastL = 0    End If    lastRID = thisRID    If lastCount < 3 Then      lastL = lastL + thisL    End If    lastCount = lastCount + 1  Next  If lastCount > 0 Then    AddResultRecord recsOut, lastRID, lastL  End IfEnd SubThe "n" constant is at line 39 (If lastCount < 3 Then) - the script uses 3.Running the script creates a new table named "Result" (or "Result 2", "Result 3", etc, if the name is already taken, as usual).
 Mike Pelletier1,629 post(s) #22-Apr-14 19:46 Wow! Thanks a ton Adam. That script works well and very fast on my data set. You have saved me lots of time and added a very nice addition to my method. It is somewhat surprising but the overall method for measuring "ruralness" along the highways does seem to work relatively well. It will be interesting to see how much traction or impact on public policy comes from this bit of GIS wizardry.
 dale535 post(s) #23-Apr-14 01:03 Mike, funny enough, we are working on something that parallels your work. Could you contact me off forum? (see my profile for email addy.) off topic. We need a forum beverage escrow service to send posters a beverage or two. (Adam, Tim, Art...) It's pleasing to see the forum tempo picking upDale
 Mike Pelletier1,629 post(s) #23-Apr-14 15:30 Will do Dale and yes these guys have more than earned it. Even though I mention the help received from all over the world, people still sometimes give me too much credit for my little projects. I think the forum shows how much more fun it is to use Manifold vs other GIS software despite the quite public face of Manifold.