Link to home
Start Free TrialLog in
Avatar of gabba_the_hutt
gabba_the_hutt

asked on

Interesting Question

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

TABLE: tblQueues
QueueID
QueueName

TABLE: tblQueueLocationCouplings
QueueID
LocationID
OrderForLoc

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

@pLocationID int
AS
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.

ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gabba_the_hutt
gabba_the_hutt

ASKER

Yes that makes the query sort properly which leaves the problem of the DISTINCT selection
Why not try putting back the DISTINCT clause:

@pLocationID int
AS
SELECT DISTINCT tblQueues.QueueID, QueueName
FROM tblQueues INNER JOIN tblQueueLocationCouplings  
          ON  tblQueueLocationCouplings.QueueID = tblQueues.QueueID
ORDER BY CASE WHEN tblQueueLocationCouplings.LocationID = @pLocationID THEN 0 ELSE 999 END, OrderForLoc
Unfortunately it seems the DISTINCT clause requires that each column in the ORDER BY clause be in the SELECT clause. That is the error I recieve in Visual Studio. Furthermore I don't believe SQL server supports "DISTINCT ON" either which was my very next port of call.
How about this one:

@pLocationID int
AS
SELECT tblQueues.QueueID, QueueName, CASE WHEN tblQueueLocationCouplings.LocationID = @pLocationID THEN 0 ELSE 999 END, MIN(OrderForLoc) AS OrderForLoc
FROM tblQueues INNER JOIN tblQueueLocationCouplings  
          ON  tblQueueLocationCouplings.QueueID = tblQueues.QueueID
GROUP BY tblQueues.QueueID, QueueName
ORDER BY CASE WHEN tblQueueLocationCouplings.LocationID = @pLocationID THEN 0 ELSE 999 END, OrderForLoc
I recieve the followiing error

ADO error: Column 'tblQueueLocationCouplings.LocationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP By clause.

I am going try something using a temporary table.
Nope.. no luck there either with the temporary table, I thought that I would be able to

Create a temp table and insert the query into it which would do the calculated columns and sorting

Then I could run a DISTINCT query over that table however the INSERT into the new table doesn't seem to maintain that order.

CREATE TABLE #QPositions (
      QueueID int,
      QueueName nvarchar(50)
)

INSERT  #QPositions (      QueueID, QueueName)
SELECT tblQueues.QueueID, QueueName
FROM tblQueues INNER JOIN tblQueueLocationCouplings  
          ON  tblQueueLocationCouplings.QueueID = tblQueues.QueueID
ORDER BY CASE WHEN tblQueueLocationCouplings.QueueLocation = @pLocationID THEN 0 ELSE 999 END, OrderForLoc

SELECT DISTINCT QueueID, QueueName FROM #QPositions

Then I thought maybe I could slap in an identity column into the temporary table to maintain order but then that would foil my evil plan to apply the DISTINCT clause

I am starting to consider getting the list and making it distinct myself in the C# code
Try this:

SELECT tblQueues.QueueID, QueueName, IDENTITY(INT, 1, 1) AS IdentityColumn
INTO #QPositions
FROM tblQueues INNER JOIN tblQueueLocationCouplings  
          ON  tblQueueLocationCouplings.QueueID = tblQueues.QueueID
ORDER BY CASE WHEN tblQueueLocationCouplings.QueueLocation = @pLocationID THEN 0 ELSE 999 END, OrderForLoc

SELECT QueueID, QueueName
FROM #QPositions A
WHERE IdentityColumn = (SELECT MIN(IdentityColumn) FROM #QPositions B
                                      WHERE A.QueueID = B.QueueuID AND A.QueueName = B.QueueName)

ORDER BY IdentityColumn

or this

SELECT QueueID, QueueName
FROM #QPositions
GROUP BY QueueID, QueueName
ORDER BY MIN(IdentityColumn)