UNION SELECT controlling row order

[MichaelWClark]

I have a bit of a unique situation. I am loading a DropDownList (asp.net) from a SQL DB. Let me explain my scenario.

[Products] [DB2.dbo.Orders]
-Product_ID -Order_ID
-Product_Name -Order_Product_Name
-Product_Price -Order_Product_Price
-Order_QTY

[DROP DOWN] [PRICE_LABEL]* [OrderQTY] +ADD
ProdName1
ProdName2
ProdName3

[PRICE_LABEL]: *(gets pulled by Select Product_Price where Product_Name=DropDown.Selected)

After I add the info on the page it Inserts into the DB2.dbo.ORDERS table. Then I have another row Select that information back out for an edit Row. The issue is when I render the DropDown I need to select ALL of the Product Names BUT, I need the Product Name Stored in Orders to be the default value. I thought I could do something like this:

SELECT * FROM dbo.PRODUCTS WHERE PRODUCT_NAME = 
(select 
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT.PURCHASE_ORDER_OFFICE_EQUIPMENT FROM
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT
WHERE 
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT.PURCHASE_ORDER_OFFICE_KEY = 157)
UNION
SELECT * FROM dbo.PRODUCTS WHERE PRODUCT_NAME != 
(select 
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT.PURCHASE_ORDER_OFFICE_EQUIPMENT FROM
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT
WHERE 
BHPropertyInfo.dbo.PURCHASE_ORDER_OFFICE_EQUIPMENT.PURCHASE_ORDER_OFFICE_KEY = 157)

as to avoid duplicates and keep it Ordered correctly...unfortunately this will always return the same results as SELECT * FROM PRODUCTS... which technically is what i need...i just want the first row to correspond with ORDER_EQUIPMENT_NAME...

Please advise I know there should be a way to do this in my SQL statement.

[tesuji]

Your sql statement seems to be not correct. Probably there will be errors if it is executed. (possible reason: more than one value in resultset of subselects)

Most likely the "=" or "!=" in front of the subselects are wrong and should be replaced by "in", or even combined with "any", "some" or "all" (hoever, which would be more complex).

Unfortunately, your explantation of the scenario is rather cloudy.

-- tesu

[MichaelWClark]

Tesu,

The sub-selects are checked against a Unique Key as to not return more than one result. Technically I should use a 'SELECT TOP 1' clause but I am trying to keep my Query as clean as possible. Let me try to rephrase my scenario so it is better understood.

I have two databases. Database A holds all of my products and prices. Database B holds all of the Purchase Order info (what property name, purchaseID, product Quantity, product ID ..etc.).

My script pulls all the products that are for a particular property (aka USER) and that also have a corresponding purchase Order ID (As they will have many purchase orders throughout the life of this script). All of this data is put into a table (gridview in asp.net actually, but this is not important) for viewing and editing.

The issue comes into play with editing. Say my product table has 3 products in it.

HardDrive
Monitor
Processor

If this partuclar order has 2 products requested for example:

HardDrive qty:2
Processor qty:4

and there is an error in one line I am allowing the info to be edited by the script. My user hits an edit button to the right of the ROW returned and then the script will replace the ProductName value with a dropdown that is being populated by the Products DB. The big issue is the Products DB will populate this dropdown in alphabetical order right now. I need a way to have it return the existing ProductName that is in use currently, and THEN list the remaining products below.

I hope this clears the scenario up a bit..I just dont want someone to hit the edit button and then not realize that the product name changed. This will cause more frustration to the end user...which is never a good thing.

Please advise and again thank you for any and all help.

[tesuji]

Now I have got some better understanding of your problem. I am supposing you are using adodb. So why not populating your dropdown explicitly, in principle:

1. create an adodb recordset object
2. create your select statement to get the ProductName from db, order doesn´t matter.
3. connect and open select
************
4. populate dropdown with your existing ProductName, makes first row  on top of dropdown 
************
5. Populate dropdown in while-loop with other ProductName from products (now I do understand 
   why you made the UNION, first predicate with =, 2nd predicate with != )

do while not eof
  here populate dropdown list with ProductName from table products 
  excluding already existing ProductName (simple if) 
  move next
loop

Hopefully, that your wanting: "I need a way to have it return the existing ProductName that is in use currently, and THEN list the remaining products below." could be fulfilled, at least above pseudo code may inspire you a little.

(I don't know ASP, however I did some adodb programming a couple of years ago)

-- tesu

[MichaelWClark]

Yes I think I am going to set the dropdown up dynamically in code, Your psudo code does help me to clean up my SQL a little and I thank you for that. I knew it was an awkward way to code select if = then select if !=...but I was just trying to do a select * where and order it in a certain manner. Again thanks! :)

[1stDAN]

Hello Michael

I am sure that your idea using UNION is right way, as for this example works fine.

/* only one row is number 1 */
select custname as cust, 1 as rown from customers where custid = 10000 /* Xaver's id */
union
/* all other customers except Xaver, starting with row# 2 */
select custname as cust, 1+row_number() over (order by custname) as rown from customers where custid != 10000
/* this orders the whole unified set not only the 2nd one! */
order by rown asc;  
/*
cust       rown 
Xaver        1
Alvert       2
Chris        3
Herby        4
Xanthippe    5
Zoro         6
*/

Now customer list is ordered by customer's name and Xaver is still in first row position.

row_number() is standard sql from olap extension what every database has available today. Because of olap the over (order by) is syntactically necessary and makes really sense from the viewpoint of olap. SQL server 2005 also has row_number function.

[MichaelWClark]

Oh wow, Beautiful syntax and it makes me feel a little better that I was close to the correct solution. In this case I ended up using my program's code to sort it all out and had to use 2 seperate sql calls...which was not optimal for preformance, BUT now I know how to properly do this sort of thing and will not have such issues in the future. Thank you for the help!

[1stDAN]

No thanks!

The other solution with ADO seems to be more flexible but I just wanted to show you that your solution also works with extension row_number().