Sort order for ranking query

[AFrost]

Hi all,
I have a query in which I am ranking artists who are assigned to shows.

Here is the query:
SELECT qryPS1TeamsFilter.ShowID, qryPS1TeamsFilter.ShowName, tblArtists.FirstName, tblArtists.LastName, (select count(*)+1 from [qryShowsArtists] as b WHERE [qryPS1TeamsFilter].ShowID = b.ShowID AND b.ArtistID < [qryShowsArtists].ArtistID) AS rank, qryShowsArtists.ArtistID
FROM tblArtists INNER JOIN (qryPS1TeamsFilter INNER JOIN qryShowsArtists ON qryPS1TeamsFilter.ShowID = qryShowsArtists.ShowID) ON tblArtists.ArtistID = qryShowsArtists.ArtistID
GROUP BY qryPS1TeamsFilter.ShowID, qryPS1TeamsFilter.ShowName, tblArtists.FirstName, tblArtists.LastName, qryShowsArtists.ArtistID
ORDER BY qryPS1TeamsFilter.ShowName, tblArtists.FirstName;

The issue I am having is that the ranking results are ordered by the Artist ID number (ArtistID). The lowest Artist ID number is assigned a rank of 1 and the next lowest is a rank of 2 etc.

I would like the ranking be ordered by the Artist's first name (tblArtists.FirstName).

Is there a way of modifying the query to order the ranking by FirstName instead of ArtistID?

I have not included any details of why I need to do this, or details of the tables and other queries referenced in the SQL statement above but would be happy to provide more details if they are needed.

Thanks for your consideration!

 

[Guus2005]

SELECT qryPS1TeamsFilter.ShowID, qryPS1TeamsFilter.ShowName, tblArtists.FirstName, 
tblArtists.LastName, ([COLOR="Red"]select count(*)+1 from [qryShowsArtists] as b WHERE [qryPS1TeamsFilter].ShowID = b.ShowID AND b.ArtistID < [qryShowsArtists].ArtistID[/COLOR]) AS rank, 
qryShowsArtists.ArtistID
FROM tblArtists INNER JOIN (qryPS1TeamsFilter INNER JOIN qryShowsArtists 
ON qryPS1TeamsFilter.ShowID = qryShowsArtists.ShowID) ON tblArtists.ArtistID = qryShowsArtists.ArtistID
GROUP BY qryPS1TeamsFilter.ShowID, qryPS1TeamsFilter.ShowName, tblArtists.FirstName, tblArtists.LastName, qryShowsArtists.ArtistID
ORDER BY qryPS1TeamsFilter.ShowName, tblArtists.FirstName;

The subquery (red) and the main query don't match. In the subquery you are counting ...b.ArtistID < [qryShowsArtists].ArtistID why is that? Why do you add 1 to the result: count(*)+1?

Why would you want to rank an artist by his name?

questions, questions...

HTH