View Full Version : grouping by category in recordsets

08-19-2004, 01:34 PM
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


08-19-2004, 08:29 PM
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