Welcome to the SQLitening support forums!
Started by bobl, April 29, 2011, 02:08:46 pm
QuoteI've attempted this a couple of ways but my efforts look a bit drawn-out so...is there a slick way of going down a table column and reporting...each group's start id and number of recs eg...tbl==id col1 col2=========1 a2 b3 b4 b5 d6 b7 bwould give the following result1,12,35,16,2
Local ct as long 'Counter varLocal tmp as string 'hold the temp value or col1Local StartID as long 'hold the the first ID value for that col1 valueLocal res as string 'the resulting listslSel "SELECT * FROM tbl ORDER BY col1, ID"Do While slGetRow if slFN("col1") <> tmp then 'Write the values to a list If Len(tmp) then res += Format$(StartID) & $Tab & tmp & $Tab & Format$(ct) & $CrLf End If tmp = slFN("col1") 'set the new value to count ct = 0 'reset the counter StartID = slFN("ID") 'Remember the start value for ID End If Incr ctLoop'Show the list? res'This should give you the following list:'ID Tmp Ct'1 a 1'2 b 3'5 d 1'6 b 2
QuoteI was hoping Fred might come up with an advanded SELECT statement, if that is possible at all. But that simply surpasses my knowledge.