Need to change connection Source for sorting ASP page

Hello! First post here. Total newbie so please go easy on me.
 
I have an ASP page that has 10 items listed on it that are pulled from a database. I would like to be able to change the order of the items based on certain criteria, all of which is in the same database but in different tables. Currently the items are sorted by Name which is in the same table. I would like to add an option of sorting the items by Highest Rating, which is in a different table same db, and is achieved by a rating calculation function.
 
My connection Source looks like this:

rsItems.Source = "SELECT * FROM Items WHERE Approved = True ORDER BY ItemName ASC"

 
My rating function is called by this:

<%=DisplayRating(rsItems.Fields.Item("ItemNo").Value)%>

 
I tried using this example (http://www.w3schools.com/ado/showasp.asp?filename=demo_sort_3) but it doesn't show how to change the "ORDER BY" using a different table in same db.
 
Can someone offer a little assistance? It would be greatly appreciated!

If your "highest rating" field is in a different table, then you need to JOIN the two tables. ORDER BY works only on the selected data set.

Thank you for your response, 59cobalt. Here's what I'm thinking now:

<a href=”filename.asp?sort=rating”>Sort by Ratings</a>
<a href=”filename.asp”>Sort by Name</a>
 
<%
if request.querystring("sort")<>"" then
sort=request.querystring("sort")
else
sort="ItemName"
end if
 
Source=“SELECT items.ItemName, ratings.(DisplayRating ?)
FROM items
WHERE Approved=True
FULL JOIN ratings
ON items.ItemName=ratings.(DisplayRating ?)
ORDER BY ” & sort  (not sure how to do ASC or DEC after sort ?)
%>

Will “DisplayRating” results pull from the function on the page?

Lon
Source=“SELECT items.ItemName, ratings.(DisplayRating ?)
FROM items
WHERE Approved=True
FULL JOIN ratings
ON items.ItemName=ratings.(DisplayRating ?)
ORDER BY ” & sort  (not sure how to do ASC or DEC after sort ?)

The ON clause is wrong (or the DisplayRating field is grossly mis-named). You need to JOIN the two tables on a matching field. For instance, if both tables contain the field ItemName, then you'd join the tables on that field ("ON items.ItemName=ratings.ItemName"). And are you sure you want a FULL (OUTER) JOIN, not an INNER JOIN? The former might produce rows with NULL values. If you're unfamiliar with the difference between them, check the Wikipedia article.

As for sorting, the default sort order is ASC (smallest values first). If you want the largest values first you have to append the DESC keyword.

Your query should look somewhat like this:

"SELECT items.ItemName, ratings.DisplayRating
FROM items
 FULL JOIN ratings ON items.ItemName = ratings.ItemName
WHERE items.Approved = True
ORDER BY " & sort & " DESC"

Thanks again for your response, 59cobalt! I don't know what the 'ON clause' should be, that's why I showed it the way I did. The field name is not the same in both tables. In fact, the total rating is not even in a table. There is a function on the page that calculates the total rating for each Item and then displays it on the page next to the item. And I would like to be able to sort the page (all items) by that total rating. The function pulls from a separate table in the database that has the ItemName, Rating, and an IP Address to keep track of who already rated the item. So the function totals the ratings and divides by qty to get an average. I can try to create a diagram if that makes it easier to understand. Sorry if I didn't make this clear. Like I said, I'm a newbie and I don't know all of the correct terminology and all. :)

Lon
Thanks again for your response, 59cobalt!

You're welcome.

Lon
I don't know what the 'ON clause' should be, that's why I showed it the way I did. The field name is not the same in both tables. In fact, the total rating is not even in a table.

The ON keyword is part of the JOIN clause and defines which fields should be used for joining the two tables. The field name doesn't need to be the same in both tables, but you have to have matching values in both fields. Without that your JOIN won't be able to correlate the records in those tables.

Lon
There is a function on the page that calculates the total rating for each Item and then displays it on the page next to the item. And I would like to be able to sort the page (all items) by that total rating. The function pulls from a separate table in the database that has the ItemName, Rating, and an IP Address to keep track of who already rated the item. So the function totals the ratings and divides by qty to get an average.

Is that function implemented in the database or is it implemented in ASP? Normally this kind of thing is best done in the database. And how is the overall rating calculated anyway? Is it the average of the ratings per item? In that case you should be able to do this entirely in SQL:

SELECT ItemName, AVG(Rating) AS "Average Rating"
FROM tblItemRatings
GROUP BY ItemName
ORDER BY "Average Rating" DESC

Edit: You wanted the items ordered by rating, of course.