Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL - ORDER BY clause

Status
Not open for further replies.

cindy0904

Technical User
May 30, 2002
29
US
Hi, does anyone know how to alter this code so the ips.asp page (code below) sorts the table "Stats" ORDER BY Date, Time? Or, possibly since the date is passed in a query string (as per below) then maybe only sort by TIME since the day has been established? Confusing.

Thanks for any help.

--Cindy


**************************************
<!--#include file=&quot;config.asp&quot;-->
<%
'-------------------------------------------------------------
'StatCounteX 3.1
'
'File: config.asp
'Description: Graphical Report Generator
'Initiated by Kevin Yochum on Feb 5, 2001
'-------------------------------------------------------------

'You may use the code for any purpose
'But re-publishing is discouraged.
'See License.txt for additional information

'-------------------------------------------------------------
'Change Log:
'-------------------------------------------------------------
'# Feb 12, 2001 by Hakan Eskici
'Added missing </table> tags
'-------------------------------------------------------------

' Sub ListIps
'
' Usage:
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ListIps( lYear, lMonth, lDay, lHour )

sDataSource = &quot;&quot;
If Len(lHour) > 0 Then
sDataSource = &quot;GroupIpsByHourAndDate&quot;
Else
sDataSource = &quot;GroupIpsByDate&quot;
End If

sSQL = &quot;SELECT * From &quot; & sDataSource & &quot; Where 1=1 &quot;

If Len( lYear ) > 0 Then
sSQL = sSQL & &quot;and DatePart(&quot;&quot;yyyy&quot;&quot;,[Date])=&quot; & lYear & &quot; &quot;
End If

If Len( lMonth ) > 0 Then
sSQL = sSQL & &quot;and DatePart( &quot;&quot;m&quot;&quot;, [Date])= &quot; & lMonth & &quot; &quot;
End If

If Len( lDay ) > 0 Then
sSQL = sSQL & &quot;and DatePart( &quot;&quot;d&quot;&quot;, [Date])= &quot; & lDay & &quot; &quot;
End If

If Len( lHour ) > 0 Then
sSQL = sSQL & &quot;and Hour= &quot; & lHour & &quot; &quot;
End If

' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable

%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = &quot;#ffe4b5&quot;>
<tr>
<td bgcolor=&quot;#ffe4b5&quot; width=10>»</td>
<td class=&quot;smallerheader&quot; bgcolor=&quot;#ffe4b5&quot;>Stats: Visitors on <%=GetDate%></td>
<td bgcolor=&quot;#ffe4b5&quot; width=10></td>
</tr>
<%
do while not rs.eof

sLink = &quot;<a href=&quot;&quot;ips.asp?ip=&quot; & rs(&quot;IP&quot;)

If Len( lYear ) > 0 Then
sLink = sLink & &quot;&year=&quot; & lYear
End If

If Len( lMonth ) > 0 Then
sLink = sLink & &quot;&month=&quot; & lMonth
End If

If Len( lDay ) > 0 Then
sLink = sLink & &quot;&day=&quot; & lDay
End If

If Len( lHour ) > 0 Then
sLink = sLink & &quot;&hour=&quot; & lHour
End If

sLink = sLink & &quot;&quot;&quot;>&quot; & rs(&quot;IP&quot;) & &quot;</a>&quot;
%>
<tr>
<td bgcolor=&quot;#fffaf0&quot;></td>
<td bgcolor=&quot;#fffaf0&quot;><%=sLink%></td>
<td bgcolor=&quot;#fffaf0&quot;></td>
</tr>
<%
rs.movenext
loop
%>
<tr>
<td bgcolor=&quot;#fffaf0&quot;></td>
<td bgcolor=&quot;#fffaf0&quot;><%=rs.RecordCount%> visitors</td>
<td bgcolor=&quot;#fffaf0&quot;></td>
</tr>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub


'
' Sub ShowClickPath
'
' Usage:
' sIP - The IP to show the click path for.
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ShowClickPath( sIp, lYear, lMonth, lDay, lHour )

If Len( sIP ) = 0 Then
Response.Write( &quot;Error: IP Address not provided for displaying a Click Path.&quot; )
Exit Sub
End If

' sSQL = &quot;SELECT Stats.*, Refs.RefName, Paths.PathName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID WHERE (((Stats.IP)='&quot;&sIp&&quot;') AND ((Stats.Date)=&quot;&sDate&&quot;))&quot;
sSQL = &quot;SELECT Stats.Date, Stats.Time, Stats.IP, Paths.PathName, Refs.RefName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID Where Stats.IP='&quot; & sIp & &quot;'&quot;

If Len( lYear ) > 0 Then
sSQL = sSQL & &quot; and DatePart(&quot;&quot;yyyy&quot;&quot;, [Stats].[Date]) = &quot; & lYear
End If

If Len( lMonth ) > 0 Then
sSQL = sSQL & &quot; and DatePart(&quot;&quot;m&quot;&quot;, [Stats].[Date]) = &quot; & lMonth
End If

If Len( lDay ) > 0 Then
sSQL = sSQL & &quot; and DatePart(&quot;&quot;d&quot;&quot;, [Stats].[Date]) = &quot; & lDay
End If

If Len( lHour ) > 0 Then
sSQL = sSQL & &quot; and DatePart(&quot;&quot;h&quot;&quot;, [Stats].[Time]) = &quot; & lHour
End If

' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable

sFieldName = &quot;&quot;
If bShowLinks And InStr( rs(&quot;RefName&quot;), &quot; ) > 0 Then
sFieldName = &quot;<a href=&quot;&quot;&quot; & rs(&quot;RefName&quot;) & &quot;&quot;&quot;>&quot; & rs(&quot;RefName&quot;) & &quot;</a>&quot;
Else
sFieldName = rs(&quot;RefName&quot;)
end if
%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = &quot;#ffe4b5&quot;>
<tr>
<td bgcolor=&quot;#ffe4b5&quot; width=10>»</td>
<td colspan=3 class=&quot;smallerheader&quot; bgcolor=&quot;#ffe4b5&quot;>Stats: Click path for <%=sIp%> on <%=rs(&quot;Date&quot;)%></td>
<td bgcolor=&quot;#ffe4b5&quot; width=10></td>
</tr>
<tr>
<td bgcolor=&quot;#fffaf0&quot;></td>
<td bgcolor=&quot;#fffaf0&quot;><%=rs(&quot;Date&quot;)%></td>
<td bgcolor=&quot;#fffaf0&quot;><%=rs(&quot;Time&quot;)%></td>
<td bgcolor=&quot;#fffaf0&quot;><%=sFieldName%></td>
<td bgcolor=&quot;#fffaf0&quot;></td>
</tr>
<%

' Calculate totals
do while not rs.eof

sFieldName = &quot;&quot;
If bShowLinks And InStr( rs(&quot;PathName&quot;), &quot; ) > 0 Then
sFieldName = &quot;<a href=&quot;&quot;&quot; & rs(&quot;PathName&quot;) & &quot;&quot;&quot;>&quot; & rs(&quot;PathName&quot;) & &quot;</a>&quot;
Else
sFieldName = rs(&quot;RefName&quot;)
end if
%>
<tr>
<td bgcolor=&quot;#fffaf0&quot;></td>
<td bgcolor=&quot;#fffaf0&quot;><%=rs(&quot;Date&quot;)%></td>
<td bgcolor=&quot;#fffaf0&quot;><%=rs(&quot;Time&quot;)%></td>
<td bgcolor=&quot;#fffaf0&quot;><%=sFieldName%></td>
<td bgcolor=&quot;#fffaf0&quot;></td>
</tr>
<%
rs.movenext
loop
%>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub

Function GetDate
If Len(Request.QueryString(&quot;Month&quot;)) > 0 Then
GetDate = MonthName(Request.QueryString(&quot;Month&quot;))

If Len(Request.QueryString(&quot;Day&quot;)) > 0 Then
GetDate = GetDate & &quot; &quot; & Request.QueryString(&quot;Day&quot;)
End If

GetDate = GetDate & &quot;, &quot;
End If

If Len(Request.QueryString(&quot;Year&quot;)) > 0 Then
GetDate = GetDate & Request.QueryString(&quot;Year&quot;)
End If

If Len(GetDate) = 0 Then
GetDate = &quot;All Data&quot;
End If
End Function

sIp = Request.QueryString( &quot;IP&quot; )
sYear = Request.QueryString( &quot;Year&quot; )
sMonth = Request.QueryString( &quot;Month&quot; )
sDay = Request.QueryString( &quot;Day&quot; )
sHour = Request.QueryString( &quot;Hour&quot; )

%>
<!DOCTYPE HTML PUBLIC &quot;-//W3C//DTD HTML 4.0 Transitional//EN&quot;>
<html>
<head>
<title>StatCounteX 3.1</title>
<link rel=&quot;stylesheet&quot; type=&quot;text/css&quot; href=&quot;scx.css&quot;>
</head>

<body>
<table border=0 width=&quot;100%&quot;>
<td class=&quot;title&quot;>
StatCounteX 3.1 Reports
</td>
<td class=&quot;smallertext&quot; align=right>
<a href=&quot; target=&quot;_blank&quot;>Report a Bug</a> |
<a href=&quot; target=&quot;_blank&quot;>Recommend a feature</a> |
<a href=&quot; target=&quot;_blank&quot;>Ask a question</a> |
<a href=&quot; target=&quot;_blank&quot;>Submit a site</a>
</td>
</table>
<hr size=&quot;1&quot; color=&quot;#C0C0C0&quot; noshade>
» <a href=&quot;reports.asp&quot;>Reports</a> » <a href=&quot;reportpathy.asp&quot;>Yearly Report</a>
» <a href=&quot;reportpathm.asp?year=<%=sYear%>&quot;>Monthly Report</a>
» <a href=&quot;reportpathd.asp?year=<%=sYear%>&month=<%=sMonth%>&quot;>Daily Report</a>
<%
If Len( sIp ) > 0 Then
%>
» <a href=&quot;ips.asp?year=<%=sYear%>&month=<%=sMonth%>&day=<%=sDay%>&quot;>Visitors Report</a> » Click Path
<%
Else
%>
» Visitors Report
<%
End If
%>
<br><br>
<%
Response.Write( &quot;<b>Displaying Data For &quot; & GetDate & &quot;</b><br><br>&quot; )

If Len( sIp ) > 0 Then
ShowClickPath sIp, sYear, sMonth, sDay, sHour
Else
ListIps sYear, sMonth, sDay, sHour
End If
%>

<a href=&quot;<%= Request.ServerVariables(&quot;HTTP_REFERER&quot;) %>&quot;>Back to Previous Page</a>

<hr size=&quot;1&quot; color=&quot;#C0C0C0&quot; noshade>
<table border=0 width=&quot;100%&quot;>
<tr>
<td class=&quot;smallertext&quot;>
Visit <a href=&quot; for more OpenSource VB and ASP Projects.
</td>
<td align=&quot;right&quot;>
<a href=&quot; <img src=&quot;icostatcountex.gif&quot; width=90 height=30 alt=&quot;StatCounteX&quot; border=&quot;0&quot;>
</a>
</td>
</tr>
</table>

</body>
</html>
 
At the end of your query string add &quot;ORDER BY TIME&quot;, or whatever you fieldname is for the Time, and either ASC or DESC depending on what you want.

For example;
SQL = &quot;SELECT * FROM TABLE WHERE FIELD1='2' ORDER BY FIELD2 ASC&quot;
 
Hi, I did change this line before, adding &quot;ORDER BY TIME ASC&quot; but no change in how the page displays (the page orders by IP numerical order currently).

Thanks for any other ideas.

Cindy
* * * * * * * * *

sSQL = &quot;SELECT Stats.Date, Stats.Time, Stats.IP, Paths.PathName, Refs.RefName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID Where Stats.IP='&quot; & sIp & &quot;' ORDER BY TIME ASC&quot;
 
Are you sure you added it to the correct sql statement?
Code:
   If Len( lHour ) > 0 Then
      sSQL = sSQL & &quot;and Hour= &quot; & lHour & &quot; &quot;
   End If
'Add order here
Code:
   sSQL = sSql & &quot; ORDER BY [Stats].[Date]&quot;

   ' Connect to the database

If so, I can't see any reason it shouldn't be ordering correctly.

Also, before you start your loop to Rs.EOF you should do a rs.MoveFirst because your not guaranteed that the pointer will be pointing to the first record in the set when it is returned ordered.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
 
THANKS SO MUCH! You're absolutely right, I was adding the code in the wrong spot. I appreciate you taking the time to help!

Cindy
 
No problem :)
I was hoping it would be that simple (I often misplace my left shoe on my right foot) because if it wasn't I would have been at a complete loss, then I would have spent all day working on this instead of (my) real work :p

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top