Display Recordset in different order 1

[jon24422531]

Hi guys

I am running a query against our SQL database:

SELECT DISTINCT(P.WorkLogNo) AS [Project ID], U.UserFullName AS Owner, (P.Urgency * P.Priority) AS [Rating], W.FaultType AS [Area], P.Title, 
CONVERT(char(20),W.AmendedStamp,0) as [Last Update], 
ISNULL(W.LastUser, 'N/A') AS LastUser 
from WorkLog_Projects P 
JOIN Worklog W on P.WorkLogNo = W.FaultNo 
LEFT OUTER JOIN Users U on W.userID = U.UserID 
LEFT OUTER JOIN WorkLog_History H on  W.FaultNo = H.WorklogNo

I get the recordset and display it in a table,

<table border='0'>
<tr>
<%
	for each fieldItem in rsData.fields
		Response.Write vbTab & vbTab &"<td bgcolor=""#EBDDE2""><strong>" & fieldItem.name
		Response.Write "</strong></td>" & vbCrLf
	next
		Response.Write vbTab & "</tr>" & vbCrLf
%>
</tr>
<%

Do While not rsData.EOF
	Response.Write "<tr>"
	for each fieldItem in rsData.fields
		if fieldItem.name = "Project ID" then
	Response.Write "<td>&nbsp;<a href=""ProjectAdvanced.asp?id=" & fieldItem.Value & """>" & fieldItem.value & "</a></td>"
		else
	Response.Write "<td><small>&nbsp;" & fieldItem.value & "</td>"
		end if
	next
	Response.Write "</tr>"
	rsData.MoveNext
Loop
%>
</table>

However although I am returning the P.WorkLogNo first (as it is a DISTINCT select) I want to display it in a different order.
Can I change the field order in ASP?
Or should I do the SELECT in to a Table Variable (@Table) and then do the select in a different order?

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

Distinct is not a function. Sure.... it appears to be a function because you put parenthesis around it, but it really isn't a function. It affects all the columns returned.

Remove the parenthesis from the WOrkLogNo column. Change the column order to anything you want, and try the code.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

George

Thanks. I always assumed that you could only use DISTINCT with the first column in the SELECT statement and always used parenthesis. I did a small experiment where I made two other records identical apart from one field and tried the query again, it worked.

Thanks again

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

Another "SQL Myth" busted!

Actually, it's a rather common misconception.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom