Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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.

0
gabba_the_hutt
Asked:
gabba_the_hutt
  • 4
  • 4
1 Solution
 
rafranciscoCommented:
If I understand your question correctly, to sort your output according to your requirement:

@pLocationID int
AS
SELECT 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
0
 
gabba_the_huttAuthor Commented:
Yes that makes the query sort properly which leaves the problem of the DISTINCT selection
0
 
rafranciscoCommented:
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
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
gabba_the_huttAuthor Commented:
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.
0
 
rafranciscoCommented:
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
0
 
gabba_the_huttAuthor Commented:
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.
0
 
gabba_the_huttAuthor Commented:
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
0
 
rafranciscoCommented:
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)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now