SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: bobl on April 29, 2011, 02:08:46 pm

Title: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: bobl on April 29, 2011, 02:08:46 pm
I see I can do COUNT on records in table using "WHERE fld=current rec value and id>current rec id"
but I only want CONSECUTIVE matches and
the above would pick up ALL matches from current rec to end of table.

I also thought about getting the id of the FIRST non-matching rec
i.e. "WHERE field<>value and id>current id" and
then do a further query to... "COUNT WHERE id>current rec id AND id<id of non matching rec" but...
it won't work where you have a matching group of recs at the END of the table
i.e. 'cos there's no non-matching rec to signal the end of the group.

I'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  a
2  b
3  b
4  b
5  d
6  b
7  b
would give the following result
1,1
2,3
5,1
6,2

Any advice much appreciated


Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: bobl on April 29, 2011, 04:17:39 pm
I've just seen WHERE NOT EXISTS so...
perhaps I could do a COUNT...
WHERE
exists(select where id>current rec's id and value=current rec's col value)
AND
not exists (select * from tbl Where id>current rec's id and value<>current value)

assuming rightly or wrongly that not exists causes the search to STOP when it encounters something
it doesn't want.

so that it would stop COUNT when it hits the first non-matching record leaving only consecutive records that match?

Is this a possibility?

Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: bobl on May 06, 2011, 02:34:47 pm
Wow... I did this by looking at each record in turn and needed to 'cos the logic was far more complex than I'd appreciated.
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Rolf Brandt on May 07, 2011, 03:41:07 am
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  a
2  b
3  b
4  b
5  d
6  b
7  b
would give the following result
1,1
2,3
5,1
6,2


If I understand you right you would need these steps to go through.

Count(b) would always give you the total number of b-values (5 in your example), so you cannot use that. You would need to go manually through the recordset. I probably would do it this way:

Use an SQL expression that puts your data in a proper order:
SELECT * FROM tbl ORDER BY col1, ID

This would present your data in this sequence:
id col1 col2
=========
1  a
2  b
3  b
4  b
6  b
7  b
5  d

Now browse through your recordset with a function like this:

Local ct as long  'Counter var
Local tmp as string 'hold the temp value or col1
Local StartID as long 'hold the the first ID value for that col1 value
Local res as string 'the resulting list

slSel "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 ct
Loop
'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



Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: bobl on May 07, 2011, 04:23:38 am
Initially...the logic seemed very straight forward and I felt a little embarrased asking but...
your re-ordering of the table looks far slicker than my method and I'd missed this COMPLETELY so...
I'll look at incorporating it.

Thank you very much for your work!
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Rolf Brandt on May 07, 2011, 04:31:06 am
We have a sayin in German: "Sometime you don't see the forest because of all the trees.".

Happens often to me too, That's why discussions at a forum are so valuable.
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: cj on May 07, 2011, 05:04:56 am
How about WHERE and ORDER BY in the select statement using the recordset column number output by number.
Using the SQLitening sample database here just demonstrating the syntax

slSel "SELECT PRICE FROM PARTS WHERE PRICE > 9 ORDER BY 1 DESC LIMIT 10"
slSel "SELECT * FROM PARTS WHERE PRICE > 9 ORDER BY 4 DESC LIMIT 10"
                                                                       
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Rolf Brandt on May 07, 2011, 11:26:40 am
Sorry, I don't get the point. (Might be one of those tree problems!)
Could you explain a little better?
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: cj on May 08, 2011, 12:29:36 am
Rolf,
Sorry, the recordsets would not be CONSECUTIVE with the order by and where in my example.
How to do with only a SELECT statement?


Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Rolf Brandt on May 08, 2011, 02:50:35 am
Hi cJ,

I think Bobl's task cannot be solved by simply a SELECT query, at least not with my limited knowledge of SQL. In my little example code I put the records alreay into proper order with this query:

"SELECT * FROM tbl ORDER BY col1, ID"

But Bobl needs the ID when a certain value (lets say "b", appears for the first time, the count of b's until value changes to something else, and again when b shows up again. That's why an aggregate function like COUNT won't work.

I was hoping Fred might come up with an advanded SELECT statement, if that is possible at all. But that simply surpasses my knowledge.
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Fred Meier on May 08, 2011, 12:39:18 pm
QuoteI was hoping Fred might come up with an advanded SELECT statement, if that is possible at all. But that simply surpasses my knowledge.

I'm late to the SQL game.  My background was IDMS, Btrieve, and homegrown
databases (like SAM on the IBM 8100) so an "advanced" Select will have to
come from someone else. 
Title: Re: count of CONSECUTIVE RECORDS that match fld value FROM CURRENT record
Post by: Rolf Brandt on May 08, 2011, 04:15:22 pm
Similar to me - I worked for many years with dBase III, Clipper, and Hank Marquis great DB/Lib in the DOS days. Then shifted to Access Dbs.