Sorting Database Records

Ordering the recordset returned from your Access database is very easy - simply specify the columns you wish to order using an ORDER BY clause. When using the ORDER BY clause, ordering will happen by default in Ascending order (lowest to highest value). To order from highest to lowest value all you do is simply add the DESC keyword.

Some examples of ordering using the ORDER BY clause can be seen below.

Below is a table called 'Members_tbl' with 3 rows of data.

Diagram 1
Order By

SELECT * FROM Members_tbl ORDER BY ID

The above SQL statement will return all the data in Ascending Order based on the ID column. The row with the ID of 1 and with Michael Wall in it will be the first returned.

If it was:
SELECT * FROM Members_tbl ORDER BY ID DESC
Then the row with Henry Cole would be the first, simple!

Ordering by Date/Time fields

Diagram 2
Order By

You will notice that from the table above (Diagram 2), all the members have joined on the same day, the only difference is the time that they have joined.

If we were to do a SELECT * FROM Members_tbl ORDER By DateJoined this would return all the data in Ascending order based on the DateJoined field. (Remember ASC ascending is the default). In this scenario 'Michael Wall' would be the first returned as he has joined just 1 minute and 2 seconds before 'Fred Humphries'.

If we were to do a SELECT * FROM Members_tbl ORDER By DateJoined DESC then this would return 'David Goose' first.

Below is sample code using the table structure in Diagram 2;

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Order By</title>
</head>
<body>
<%
'declare your variables
Dim connection, recordset, sSQL, sConnString

'assign SQL statement that will query the database to variable
sSQL="SELECT * FROM Members_tbl ORDER By DateJoined DESC"

'create an ADO connection and recordset object
Set connection = Server.CreateObject("ADODB.connection")
Set recordset = Server.CreateObject("ADODB.recordset")
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Members.mdb")
'Open the connection to the database
Connection.Open sConnString

'Open the recordset object executing the SQL
Recordset.Open sSQL, connection
'While there are records loop through them
Do while not Recordset.Eof
response.write Recordset("FirstName") & "<br>"
response.write Recordset("SurName") & "<br>"
response.write Recordset("DateJoined") & "<br><br>"
'Move on to the next record
Recordset.MoveNext
Loop

'Now close the connection and recordset objects
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>
</body>
</html>


Previous: INSERT Form Content to Database (Part 2)

Advertisements



MembersPro

MembersPro PayPal - ASP Membership software

Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.

Get your best asp web hosting provider now and save 25%