Posted on 2005-05-15
Howdy I have been trying to write a query but I just can't seem to get quite there. Here is what I have:
SQL Server 2000 Database
ASP.Net C# Application
The purpose of this query is to return a list of all queue names and ids for a drop down list. It would take the locationid as a parameter and order the results so that the queues belonging to the locationID provided are displayed first. Further more it will order those queues in a predifined manner which is why I added the column LocOrdering.
I have constructed an SP shown below
SELECT tblQueues.QueueID, QueueName, (tblQueueLocationCouplings.QueueLocation % @plocationID) AS isLocal
FROM tblQueues INNER JOIN tblQueueLocationCouplings
ON tblQueueLocationCouplings.QueueID = tblQueues.QueueID
ORDER BY isLocal, OrderForLoc
I have used % so results in which the location id matches they can be ordered to the top. I know this is stupid I know, I wanted to use something like:
tblQueueLocationCouplings.QueueLocation = @plocationID
but It didn't seem to want top play ball. Apart from that the query above works well however I only want one record returned for each queue id. SELECT DISTINCT recognises each record as distinct because it requires all columns in the order by clause to be in the select clause.
If you can solve this issue you will not only recieve all of the points I have left but my eternal admiration.