grouping by category in recordsets

I have a list in a database that I have categorised using a category field. I now want to retrieve all records but grouped by categories. how do i get my asp to loop through each category creating a recordset for each one rather than hard code a new recordset for each category i would rather it did it dynamically if thats possible ???

so eg

category travel
1 - B&B's records (recid 1)
2- rail info (recid 2)

category local info
1 - swimming baths (recid 3)
2 - museum info (recid 4)


etc etc

cheers

you can use "GROUP BY" in your SQL statement like so


SELECT category, field1, field2 FROM myTable GROUP BY category ORDER BY field1, field2


say you want to have a heading above each of the categories then change the code to include an array and loop through the array


dim objConn, rs, myArray, i
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Insert_your_connection_string_here"
myArray = Array("travel","local info")
for i = 0 to UBound(myArray)
Set rs = objConn.Execute("SELECT category, field1, field2 FROM myTable WHERE category = '" & myArray(i) & "' ORDER BY field1, field2"
If Not rs.EOF Then
Response.Write "<h3>" & myArray(i) & "</h3>"
Response.write rs("field1") 'etc....
End If
Set rs = Nothing
next