select query with grouped running number 2

[markbrum]

Hi,

I am struggling to add a running number to a select query and group them in blocks of 6.

So say my table has a field 'colours' and I run a select query where 'colour'='red'. I would like to display a new column which counts down but not 12345678 - instead it gives the first 6 selected records the number 0; the next six the number 6; the next six the number 12 etc etc.

Thanks, Mark.

 

[PHV]

Something like this ?
SELECT A.colours, A.[Sequencer field],
(Select 6*Int(Count(*)/6) From yourTable As B Where B.[Sequencer field] < A.[Sequencer field] And SomeOtherCondition) AS Rank
FROM yourTable As A
WHERE SomeOtherCondition
ORDER BY A.[Sequencer field];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[markbrum]

Hi PHV,

Thanks v much - that is exactly what I wanted!

Mark.