Selecting group using Unknown column value

Question

hi guys,
I have a large table with records like (id,name,Class)

I want to be able to do a operation "Class" wise ..

but i dont know what are all possible values for Class

Currently i have to do is use 2 queries :

Query 1: result = select distinct class from myTable;

Query 2 : for each value from result ,
classWiseRows = select * from myTable where Class=value;

then i'm doing

for each value in Q1.result {
classRows=Q2 where Class=value;
doOperation(classRows);
}

I want to be able to combine these two queries in one ..to avoid 2 trips to DB.

im using ADO.net,.net 2.0 .

Answer 1

You could just do an Order By Class and then just partition the list as you traverse it.

Answer 2

Perhaps something like

select * from myTable where class in (select distinct class from myTable)

Answer 3

Get the entire result set from your first query into a datatable

select distinct class from myTable;

And then u can filter your record by using the Select Method of the datatable.

Also you can accomplish your task by using DataView RowFilter

If you would have used a higher version say 3.5+, I would have recommended LINQ

Hope this helps