Oracle Group By, Multiple Groups

[Zorbu]

Hard to describe what I want to do, here is an example.

I have the following data

1 a
2 a
3 a
4 b
5 b
6 a
7 a
8 c
9 c

I want a group to return
a 3
b 2
a 2
c 2

but the Group by function returns as you know
a 5
b 2
c 2

I can get the result I want using VB.net by creating a new dataset as I loop through an existing one but I want to return the result directly from Oracle to Wonderware as a view. Any Ideas?

 

[johnwm]

Could be a struggle to do that in a relational database. By their nature there is no inherent 'order' or sequence in a table, unless you impose one in a view or query.

One possible resolution would be to use an extra field when you write the data whose value increments only on a change in your first field data. You can then group on the new field.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[Zorbu]

Right, I suspected as much. I thought of the extra column idea but don't really want to do it except as a last resort. I'd rather not change the table structure for this and records get added and moved around in a variety of different ways and there would be headaches involved in making sure the extra column was always filled in correctly.