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 ???
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)
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")
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....
Set rs = Nothing
Powered by vBulletin® Version 4.2.2 Copyright © 2017 vBulletin Solutions, Inc. All rights reserved.