Sorting column headings

[carmen]

I'm using the code below to display the results in rows with the column
header as hyperlinks that will sort the rows by colum heading. Is there a
way to have the results sorted (as default) in reverse order so that the
most recent are at the top of the page also when the user selects the Open
Date or Status column headings, sort the data in reverse order .
Code is below.
Thanks

<%
ppl_ctr=0
dim mthd
' Set up SELECT Statement (Order by if user selected)
If IsEmpty(Request .QueryString("O rder")) Then
SQLStatement = "Select * From calls Order by callID"
strORDER = "Ordered by ID"
Else
SQLStatement = "SELECT * FROM calls ORDER BY " &
Request.QuerySt ring("Order") & ", callID"
If (Request.QueryS tring("Order") = "callID") Then
strORDER = "Ordered by ID"
Else
strORDER = "Ordered by " & Request.QuerySt ring("Order")
End If
End If
%>
<div align="center">
<center>
<table border="0" cellpadding="0" cellspacing="0" width="100%"
style="border-collapse: collapse" bordercolor="#1 11111">
<tr>
<td align="center" width="100%">
<font size="5">Suppor t Calls</font>
</td>
</tr>
<tr>
<td align="center" width="100%">
<a href="default.a sp">Return to Support Call Home Page</a></td>
</tr>
</tr>
<tr>
<td align="center" width="100%">
&nbsp;</td>
</tr>
</table>
</center>
</div>

<% Set RScalls = dbConnection.Ex ecute(SQLStatem ent) %>

<table align="center" border="1" cellspacing="0" cellpadding="5" >
<tr bgcolor="#B1C1D 1">
<td rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=call ID">ID</a></b><br></font></td>
<td width="50%" rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=Titl e">Title</a></b></font></td>
<td rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=Open Date">Open Date</a></b></font></td>
<td rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=Recv By">Logged By</a></b></font></td>
<td rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=stat us">Status</a></b></font></td>
<td rowspan="2"><fo nt size="-1"><b><a
href="call_list .asp?Order=stat e">State</a></b></font></td>
<td colspan="3">
<p align="center"> <font size="-1"><b>Severi ty</a></b></font></td>
</tr>
<tr bgcolor="#B1C1D 1">
<td><font size="-1"><b><a href="call_list .asp?Order=Seve rity1 DESC">Sev
1</a></b></font></td>
<td><font size="-1"><b><a href="call_list .asp?Order=Seve rity2 DESC">Sev
2</a></b></font></td>
<td><font size="-1"><b><a href="call_list .asp?Order=Seve rity3 DESC">Sev
3</a></b></font></td>
</tr>
<% Do While Not RScalls.EOF
callscounter=ca llscounter+1 %>
<tr>
<td>
<small>
<% If (Request.QueryS tring("Order") <> "") Then %>

<form method="Get" action="call_ca ll_list.asp" id=form24 name=form24>
<input type="hidden" name="Order"
value="<%=Reque st.QueryString( "Order") %>">
<% End If %>

<a href="update.as p?submit=Update &callID=<%=RSca lls("callID")%> ">
<%=RScalls("cal lID")%>
</a>
</td>
<td width="45%"><sm all><%=RScalls( "title") %></small>&nbsp;</td>
<td><small><%=R Scalls("OpenDat e")%></small>&nbsp;</td>
<td><small><%=R Scalls("RecvBy" )%></small>&nbsp;</td>
<td><small><%=R Scalls("status" )%></small>&nbsp;</td>
<td><small><%=R Scalls("state") %></small>&nbsp;</td>
<td><small><%=R Scalls("severit y1")%></small>&nbsp;</td>
<td><small><%=R Scalls("severit y2")%></small>&nbsp;</td>
<td><small><%=R Scalls("severit y3")%></small>&nbsp;</td>
</tr>
<% RScalls.movenex t
Loop
%>
<% If callscounter=0 Then %>
<%= "No Calls were found at this time." %>
<% End If %>
</table>

[J. Alan Rueckgauer]

Re: Sorting column headings

Carmen --

You can hard-code " DESC " to your SQLStatement, or make it variable like
your Querystring("Or der"):

SQLStatement = "Select * From calls Order by callID DESC"
SQLStatement = "SELECT * FROM calls ORDER BY " &
Request.QuerySt ring("Order") & ", callID DESC"

You can also sort different columns ASC (ascending) or DESC (descending),
for instance:

SELECT callID, customerName FROM calls ORDER BY callID DESC, customerName
ASC

would give you the calls in newer to older order, but then sort them by the
customer name column.

Do be careful, however, when using ORDER BY and make sure you avoid using
column collections that don't have a corresponding or covering index if the
dataset being sorted is large, or you're likely to impair performance (and
possibly elicit the wrath of your DBA).

Alan

=====

"carmen" <carmen@iyahoo_ Spam.com> wrote in message
news:uazrLZtbEH A.3012@tk2msftn gp13.phx.gbl...[color=blue]
> I'm using the code below to display the results in rows with the column
> header as hyperlinks that will sort the rows by colum heading. Is there a
> way to have the results sorted (as default) in reverse order so that the
> most recent are at the top of the page also when the user selects the Open
> Date or Status column headings, sort the data in reverse order .
> Code is below.
> Thanks
>
> <%
> ppl_ctr=0
> dim mthd
> ' Set up SELECT Statement (Order by if user selected)
> If IsEmpty(Request .QueryString("O rder")) Then
> SQLStatement = "Select * From calls Order by callID"
> strORDER = "Ordered by ID"
> Else
> SQLStatement = "SELECT * FROM calls ORDER BY " &
> Request.QuerySt ring("Order") & ", callID"
> If (Request.QueryS tring("Order") = "callID") Then
> strORDER = "Ordered by ID"
> Else
> strORDER = "Ordered by " & Request.QuerySt ring("Order")
> End If
> End If
> %>[/color]
[snip]

[dlbjr]

Re: Sorting column headings

Get data once.
Place in XML Data Island on Client and sort with JavaScript.
Why hit database and reload the data each time?

dlbjr
Pleading sagacious indoctrination!