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 .QueueLoca tion % @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. QueueLocat ion = @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.
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
FROM tblQueues INNER JOIN tblQueueLocationCouplings
ON tblQueueLocationCouplings.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
@pLocationID int
AS
SELECT DISTINCT tblQueues.QueueID, QueueName
FROM tblQueues INNER JOIN tblQueueLocationCouplings
ON tblQueueLocationCouplings.
ORDER BY CASE WHEN tblQueueLocationCouplings.
ASKER
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
@pLocationID int
AS
SELECT tblQueues.QueueID, QueueName, CASE WHEN tblQueueLocationCouplings.
FROM tblQueues INNER JOIN tblQueueLocationCouplings
ON tblQueueLocationCouplings.
GROUP BY tblQueues.QueueID, QueueName
ORDER BY CASE WHEN tblQueueLocationCouplings.
ASKER
I recieve the followiing error
ADO error: Column 'tblQueueLocationCouplings .LocationI D' 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.
ADO error: Column 'tblQueueLocationCouplings
I am going try something using a temporary table.
ASKER
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. QueueLocat ion = @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
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.
ORDER BY CASE WHEN tblQueueLocationCouplings.
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. QueueLocat ion = @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)
SELECT tblQueues.QueueID, QueueName, IDENTITY(INT, 1, 1) AS IdentityColumn
INTO #QPositions
FROM tblQueues INNER JOIN tblQueueLocationCouplings
ON tblQueueLocationCouplings.
ORDER BY CASE WHEN tblQueueLocationCouplings.
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)
ASKER